//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

/* Copyright (c) 2001-2008, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use _in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions _in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer _in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */


using System;
using System.Text;
using System.Collections.Generic;
using System.Reflection;
using System.Linq;
using EffiProz.Core.DataTypes;
using EffiProz.Core.Lib;
using EffiProz.Core.Rights;
using EffiProz.Core.Persist;
using EffiProz.Core.Indexes;



namespace EffiProz.Core.DbInfos
{



    // fredt@users - 1.7.2 - structural modifications to allow inheritance
    // boucherb@users - 1.7.2 - 20020225
    // - factored _out all reusable code into DIXXX support classes
    // - completed Fred's work on allowing inheritance
    // boucherb@users - 1.7.2 - 20020304 - bug fixes, refinements, better java docs
    // fredt@users - 1.8.0 - updated to report latest enhancements and changes

    /**
     * Produces a collection of tables that form the system data dictionary. <p>
     *
     * Implementations use a group of arrays of equal size to store various
     * attributes or cached instances of system tables.<p>
     *
     * Two fixed static lists of reserved table names are kept _in String[] and
     * QName[] forms. These are shared by all implementations of
     * DatabaseInformtion.<p>
     *
     * Each implementation keeps a lookup set of names for those tables whose
     * contents are never cached (nonCachedTablesSet). <p>
     *
     * An instance of this class uses three lists named sysTablexxxx for caching
     * system tables.<p>
     *
     * sysTableSessionDependent indicates which tables contain data that is
     * dependent on the user rights of the User associatiod with the Session.<p>
     *
     * sysTableSessions contains the Session with whose rights each cached table
     * was built.<p>
     *
     * sysTables contains the cached tables.<p>
     *
     * At the time of instantiation, which is part of the Database.open() method
     * invocation, an empty table is created and placed _in sysTables with calls to
     * generateTable(int) for each name _in sysTableNames. Some of these
     * table entries may be null if an implementation does not produce them.<p>
     *
     * Calls to getSystemTable(String, Session) return a cached table if various
     * caching rules are met (see below), or it will delete all rows of the table
     * and rebuild the contents via generateTable(int).<p>
     *
     * generateTable(int) calls the appropriate single method for each table.
     * These methods either build and return an empty table (if sysTables
     * contains null for the table slot) or populate the table with up-to-date
     * rows. <p>
     *
     * When the setDirty() call is made externally, the public isDirty flag
     * is set. This flag is used next time a call to
     * getSystemTable(String, Session) is made. <p>
     *
     * Rules for caching are applied as follows: <p>
     *
     * When a call to getSystemTable(String, Session) is made, if the isDirty flag
     * is true, then the contents of all cached tables are cleared and the
     * sysTableUsers slot for all tables is set to null. This also has the
     * effect of clearing the isDirty and isDirtyNextIdentity flags<p>
     *
     * if the isDirtyNextIdentity flag is true at this point, then the contents
     * of all next identity value dependent cached tables are cleared and the
     * sysTableUsers slot for these tables are set to null.  Currently,
     * the only member of this set is the SYSTEM_TABLES system table.
     *
     * If a table has non-cached contents, its contents are cleared and
     * rebuilt. <p>
     *
     * For the rest of the tables, if the sysTableSessions slot is null or if the
     * Session parameter is not the same as the Session object
     * _in that slot, the table contents are cleared and rebuilt. <p>
     *
     * (fredt@users) <p>
     * @author boucherb@users
     * @version 1.8.0
     * @since 1.7.2
     */
    class DatabaseInformationMain : DatabaseInformation
    {
         public  static SqlType CARDINAL_NUMBER = SqlInvariants.CARDINAL_NUMBER;
   public  static SqlType YES_OR_NO       = SqlInvariants.YES_OR_NO;
   public static SqlType CHARACTER_DATA  = SqlInvariants.CHARACTER_DATA;
   public static SqlType SQL_IDENTIFIER  = SqlInvariants.SQL_IDENTIFIER;
  public  static SqlType TIME_STAMP      = SqlInvariants.TIME_STAMP;

    /** The HsqlNames of the system tables. */
    protected static QNameManager.QName[] sysTableHsqlNames;

    /** Current user for each cached system table */
    protected QNameManager.QName[] sysTableSessions =
        new QNameManager.QName[sysTableNames.Length];

    /** true if the contents of a cached system table depends on the session */
    protected static bool[] sysTableSessionDependent =
        new bool[sysTableNames.Length];

    /** cache of system tables */
    protected Table[] sysTables = new Table[sysTableNames.Length];

    /** Set: { names of system tables that are not to be cached } */
    protected static HashSet nonCachedTablesSet;

    /**
     * The <code>Session</code> object under consideration in the current
     * executution context.
     */
    protected Session session;

    /** The table types HSQLDB supports. */
    protected static String[] tableTypes = new String[] {
        "GLOBAL TEMPORARY", "SYSTEM TABLE", "TABLE", "VIEW"
    };

    /** Provides naming support. */
    static DatabaseInformationMain(){
        nonCachedTablesSet = new HashSet();
        sysTableHsqlNames  = new QNameManager.QName[sysTableNames.Length];

        for (int i = 0; i < sysTableNames.Length; i++) {
            sysTableHsqlNames[i] =
                QNameManager.newInfoSchemaTableName(sysTableNames[i]);
            sysTableHsqlNames[i].schema =
                SqlInvariants.INFORMATION_SCHEMA_QNAME;
            sysTableSessionDependent[i] = true;
        }

        // build the set of non-cached tables
        nonCachedTablesSet.add("SYSTEM_CACHEINFO");
        nonCachedTablesSet.add("SYSTEM_SESSIONINFO");
        nonCachedTablesSet.add("SYSTEM_SESSIONS");
        nonCachedTablesSet.add("SYSTEM_PROPERTIES");
        nonCachedTablesSet.add("SYSTEM_SEQUENCES");
    }

    /**
     * Constructs a table producer which provides system tables
     * for the specified <code>Database</code> object. <p>
     *
     * <b>Note:</b> before 1.7.2 Alpha N, it was important to observe that
     * by specifying an instance of this class or one of its descendents to
     * handle system table production, the new set of builtin permissions
     * and aliases would overwrite those of an existing database, meaning that
     * metadata reporting might have been rendered less secure if the same
     * database were then opened again using a lower numbered system table
     * producer instance (i.e. one in a 1.7.1 or earlier distribution).
     * As of 1.7.2 Alpha N, system-generated permissions and aliases are no
     * longer recorded in the checkpoint script, obseleting this issue.
     * Checkpointing of system-generated grants and aliases was removed
     * because their existence is very close to a core requirment for correct
     * operation and they are reintroduced to the system at each startup.
     * In a future release, it may even be an exception condition to attempt
     * to remove or alter system-generated grants and aliases,
     * respectvely. <p>
     *
     * @param db the <code>Database</code> object for which this object
     *      produces system tables
     */
    public DatabaseInformationMain(Database db) : base(db) {
       
        init();
    }

    protected void addColumn(Table t, String name, SqlType type) {

        QNameManager.QName     cn;
        ColumnSchema c;

        cn = QNameManager.newInfoSchemaColumnName(name, t.getName());
        c  = new ColumnSchema(cn, type, true, false, null);

        t.addColumn(c);
    }

    /**
     * Retrieves an enumeration over all of the tables in this database.
     * This means all user tables, views, system tables, system views,
     * including temporary and text tables. <p>
     *
     * @return an enumeration over all of the tables in this database
     */
    protected Iterator allTables() {

        return new WrapperIterator(
            database.schemaManager.databaseObjectIterator(SchemaObjectTypes.TABLE),
            new WrapperIterator(sysTables, true));
    }

    /**
     * Clears the contents of cached system tables and resets user slots
     * to null. <p>
     *
     */
    protected void cacheClear() {

        int i = sysTables.Length;

        while (i-- > 0) {
            Table t = sysTables[i];

            if (t != null) {
                t.clearAllData(session);
            }

            sysTableSessions[i] = null;
        }

        isDirty = false;
    }

    /**
     * Retrieves the system table corresponding to the specified
     * tableIndex value. <p>
     *
     * @param tableIndex int value identifying the system table to generate
     * @return the system table corresponding to the specified tableIndex value
     */
    protected virtual Table generateTable(int tableIndex) {

        Table t = sysTables[tableIndex];

//        Please note that this class produces non-null tables for
//        just those absolutely essential to the JDBC 1 spec and the
//        HSQLDB core.  Also, all table producing methods except
//        SYSTEM_PROCEDURES() and SYSTEM_PROCEDURECOLUMNS() are declared final;
//        this class produces only an empty table for each, as per previous
//        DatabaseInformation implementations, whereas
//        DatabaseInformationFull produces comprehensive content for
//        them).
//
//        This break down of inheritance allows DatabaseInformation and
//        DatabaseInformationMain (this class) to be made as small as possible
//        while still meeting their mandates:
//
//        1.) DatabaseInformation prevents use of reserved system table names
//            for user tables and views, meaning that even under highly
//            constrained use cases where the notion of DatabaseMetaData can
//            be discarded (i.e. the engine operates in a distribution where
//            DatabaseInforationMain/Full and JDBCDatabaseMetaData have been
//            dropped from the JAR), it is still impossible to produce a
//            database which will be incompatible in terms of system table <=>
//            user table name clashes, if/when imported into a more
//            capable operating environment.
//
//        2.) DatabaseInformationMain builds on DatabaseInformation, providing
//            at minimum what is needed for comprehensive operation under
//            JDK 1.1/JDBC 1 and provides, at minimum, what was provided under
//            earlier implementations.
//
//        3.) descendents of DatabaseInformationMain (such as the current
//            DatabaseInformationFull) need not (and indeed: now cannot)
//            override most of the DatabaseInformationMain table producing
//            methods, as for the most part they are expected to be already
//            fully comprehensive, security aware and accessible to all users.
        switch (tableIndex) {

            case ID_SYSTEM_BESTROWIDENTIFIER :
                return SYSTEM_BESTROWIDENTIFIER();

            case ID_SYSTEM_COLUMNS:
                return SYSTEM_COLUMNS();

            case ID_SYSTEM_CONNECTION_PROPERTIES:
                return SYSTEM_CONNECTION_PROPERTIES();

            case ID_SYSTEM_CROSSREFERENCE:
                return SYSTEM_CROSSREFERENCE();

            case ID_SYSTEM_INDEXINFO:
                return SYSTEM_INDEXINFO();

            case ID_SYSTEM_PRIMARYKEYS:
                return SYSTEM_PRIMARYKEYS();

            case ID_SYSTEM_SCHEMAS:
                return SYSTEM_SCHEMAS();

            case ID_SYSTEM_CATALOGS:
                return SYSTEM_CATALOGS();

            case ID_SYSTEM_TABLES:
                return SYSTEM_TABLES();

            case ID_SYSTEM_TABLETYPES:
                return SYSTEM_TABLETYPES();

            case ID_SYSTEM_TYPEINFO:
                return SYSTEM_TYPEINFO();

            case ID_SYSTEM_USERS:
                return SYSTEM_USERS();

            case ID_SYSTEM_SEQUENCES:
                return SYSTEM_SEQUENCES();

            case ID_COLUMN_PRIVILEGES:
                return COLUMN_PRIVILEGES();

            case ID_SEQUENCES:
                return SEQUENCES();

            case ID_TABLE_PRIVILEGES:
                return TABLE_PRIVILEGES();

            case ID_INFORMATION_SCHEMA_CATALOG_NAME:
                return INFORMATION_SCHEMA_CATALOG_NAME();

            default :
                return null;
        }
    }

    /**
     * One time initialisation of instance attributes
     * at construction time. <p>
     *
     */
    protected void init() {

        // flag the Session-dependent cached tables
        Table t;

        for (int i = 0; i < sysTables.Length; i++) {
            t = sysTables[i] = generateTable(i);

            if (t != null) {
                t.setDataReadOnly(true);
            }
        }

        GranteeManager gm    = database.getGranteeManager();
        Right          right = new Right();

        right.set(GrantConstants.SELECT, null);

        for (int i = 0; i < sysTableHsqlNames.Length; i++) {
            if (sysTables[i] != null) {
                gm.grantSystemToPublic(sysTables[i], right);
            }
        }

        right = Right.fullRights;

        gm.grantSystemToPublic(SqlInvariants.YES_OR_NO, right);
        gm.grantSystemToPublic(SqlInvariants.TIME_STAMP, right);
        gm.grantSystemToPublic(SqlInvariants.CARDINAL_NUMBER, right);
        gm.grantSystemToPublic(SqlInvariants.CHARACTER_DATA, right);
        gm.grantSystemToPublic(SqlInvariants.SQL_CHARACTER, right);
        gm.grantSystemToPublic(SqlInvariants.SQL_IDENTIFIER_CHARSET, right);
        gm.grantSystemToPublic(SqlInvariants.SQL_IDENTIFIER, right);
        gm.grantSystemToPublic(SqlInvariants.SQL_TEXT, right);
    }

    /**
     * Retrieves whether any form of SQL access is allowed against the
     * the specified table w.r.t the database access rights
     * assigned to current Session object's User. <p>
     *
     * @return true if the table is accessible, else false
     * @param table the table for which to check accessibility
     */
    protected bool isAccessibleTable(Table table) {
        return session.getGrantee().isAccessible(table);
    }

    /**
     * Creates a new primoidal system table with the specified name. <p>
     *
     * @return a new system table
     * @param name of the table
     */
    protected Table createBlankTable(QNameManager.QName name) {

        Table table = new Table(database, name, TableBase.SYSTEM_TABLE);

        return table;
    }

    /**
     * Retrieves the system <code>Table</code> object corresponding to
     * the given <code>name</code> and <code>session</code> arguments. <p>
     *
     * @param session the Session object requesting the table
     * @param name a String identifying the desired table
     *      database access error occurs
     * @return a system table corresponding to the <code>name</code> and
     *      <code>session</code> arguments
     */
    public override Table getSystemTable(Session session, String name) {

        Table t;
        int   tableIndex;

        // must come first...many methods depend on this being set properly
        this.session = session;

        if (!isSystemTable(name)) {
            return null;
        }

        tableIndex = getSysTableID(name);
        t          = sysTables[tableIndex];

        // fredt - any system table that is not supported will be null here
        if (t == null) {
            return t;
        }

        // At the time of opening the database, no content is needed
        // at present.  However, table structure is required at this
        // point to allow processing logged View defn's against system
        // tables.  Returning tables without content speeds the database
        // open phase under such cases.
        if (!withContent) {
            return t;
        }

        if (isDirty) {
            cacheClear();
        }

        QNameManager.QName oldGrantee = sysTableSessions[tableIndex];
        bool  tableValid = oldGrantee != null;

        // user has changed and table is user-dependent
        if (session.getGrantee().getName() != oldGrantee
                && sysTableSessionDependent[tableIndex]) {
            tableValid = false;
        }

        if (nonCachedTablesSet.contains(name)) {
            tableValid = false;
        }

        // any valid cached table will be returned here
        if (tableValid) {
            return t;
        }

        // fredt - clear the contents of table and set new User
        t.clearAllData(session);

        sysTableSessions[tableIndex] = session.getGrantee().getName();

        // match and if found, generate.
        t = generateTable(tableIndex);

        // t will be null at this point if the implementation
        // does not support the particular table.
        //
        // send back what we found or generated
        return t;
    }

    /**
     * Retrieves a <code>Table</code> object describing the optimal
     * set of visible columns that uniquely identifies a row
     * for each accessible table defined within this database. <p>
     *
     * Each row describes a single column of the best row indentifier column
     * set for a particular table.  Each row has the following
     * columns: <p>
     *
     * <pre class="SqlCodeExample">
     * SCOPE          SMALLINT  scope of applicability
     * COLUMN_NAME    VARCHAR   simple name of the column
     * DATA_TYPE      SMALLINT  SQL data type from Types
     * TYPE_NAME      VARCHAR   canonical type name
     * COLUMN_SIZE    INTEGER   precision
     * BUFFER_LENGTH  INTEGER   transfer size in bytes, if definitely known
     * DECIMAL_DIGITS SMALLINT  scale  - fixed # of decimal digits
     * PSEUDO_COLUMN  SMALLINT  is this a pseudo column like an Oracle ROWID?
     * TABLE_CAT      VARCHAR   table catalog
     * TABLE_SCHEM    VARCHAR   simple name of table schema
     * TABLE_NAME     VARCHAR   simple table name
     * NULLABLE       SMALLINT  is column nullable?
     * IN_KEY         BOOLEAN   column belongs to a primary or alternate key?
     * </pre> <p>
     *
     * <b>Notes:</b><p>
     *
     * <code>JDBCDatabaseMetaData.getBestRowIdentifier</code> uses its
     * nullable parameter to filter the rows of this table in the following
     * manner: <p>
     *
     * If the nullable parameter is <code>false</code>, then rows are reported
     * only if, in addition to satisfying the other specified filter values,
     * the IN_KEY column value is TRUE. If the nullable parameter is
     * <code>true</code>, then the IN_KEY column value is ignored. <p>
     *
     * There is not yet infrastructure in place to make some of the ranking
     * descisions described below, and it is anticipated that mechanisms
     * upon which cost descisions could be based will change significantly over
     * the next few releases.  Hence, in the interest of simplicity and of not
     * making overly complex dependency on features that will almost certainly
     * change significantly in the near future, the current implementation,
     * while perfectly adequate for all but the most demanding or exacting
     * purposes, is actually sub-optimal in the strictest sense. <p>
     *
     * A description of the current implementation follows: <p>
     *
     * <b>DEFINTIONS:</b>  <p>
     *
     * <b>Alternate key</b> <p>
     *
     *  <UL>
     *   <LI> An attribute of a table that, by virtue of its having a set of
     *        columns that are both the full set of columns participating in a
     *        unique constraint or index and are all not null, yeilds the same
     *        selectability characteristic that would obtained by declaring a
     *        primary key on those same columns.
     *  </UL> <p>
     *
     * <b>Column set performance ranking</b> <p>
     *
     *  <UL>
     *  <LI> The ranking of the expected average performance w.r.t a subset of
     *       a table's columns used to select and/or compare rows, as taken in
     *       relation to all other distinct candidate subsets under
     *       consideration. This can be estimated by comparing each cadidate
     *       subset in terms of total column count, relative peformance of
     *       comparisons amongst the domains of the columns and differences
     *       in other costs involved in the execution plans generated using
     *       each subset under consideration for row selection/comparison.
     *  </UL> <p>
     *
     *
     * <b>Rules:</b> <p>
     *
     * Given the above definitions, the rules currently in effect for reporting
     * best row identifier are as follows, in order of precedence: <p>
     *
     * <OL>
     * <LI> if the table under consideration has a primary key contraint, then
     *      the columns of the primary key are reported, with no consideration
     *      given to the column set performance ranking over the set of
     *      candidate keys. Each row has its IN_KEY column set to TRUE.
     *
     * <LI> if 1.) does not hold, then if there exits one or more alternate
     *      keys, then the columns of the alternate key with the lowest column
     *      count are reported, with no consideration given to the column set
     *      performance ranking over the set of candidate keys. If there
     *      exists a tie for lowest column count, then the columns of the
     *      first such key encountered are reported.
     *      Each row has its IN_KEY column set to TRUE.
     *
     * <LI> if both 1.) and 2.) do not hold, then, if possible, a unique
     *      contraint/index is selected from the set of unique
     *      contraints/indices containing at least one column having
     *      a not null constraint, with no consideration given to the
     *      column set performance ranking over the set of all such
     *      candidate column sets. If there exists a tie for lowest non-zero
     *      count of columns having a not null constraint, then the columns
     *      of the first such encountered candidate set are reported. Each
     *      row has its IN_KEY column set to FALSE. <p>
     *
     * <LI> Finally, if the set of candidate column sets in 3.) is the empty,
     *      then no column set is reported for the table under consideration.
     * </OL> <p>
     *
     * The scope reported for a best row identifier column set is determined
     * thus: <p>
     *
     * <OL>
     * <LI> if the database containing the table under consideration is in
     *      read-only mode or the table under consideration is GLOBAL TEMPORARY
     *      (a TEMP or TEMP TEXT table, in HSQLDB parlance), then the scope
     *      is reported as
     *      <code>java.sql.DatabaseMetaData.bestRowSession</code>.
     *
     * <LI> if 1.) does not hold, then the scope is reported as
     *      <code>java.sql.DatabaseMetaData.bestRowTemporary</code>.
     * </OL> <p>
     *
     * @return a <code>Table</code> object describing the optimal
     * set of visible columns that uniquely identifies a row
     * for each accessible table defined within this database
     */
    Table SYSTEM_BESTROWIDENTIFIER() {

        Table t = sysTables[ID_SYSTEM_BESTROWIDENTIFIER];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_SYSTEM_BESTROWIDENTIFIER]);

            addColumn(t, "SCOPE", SqlType.SQL_SMALLINT);            // not null
            addColumn(t, "COLUMN_NAME", SQL_IDENTIFIER);         // not null
            addColumn(t, "DATA_TYPE", SqlType.SQL_SMALLINT);        // not null
            addColumn(t, "TYPE_NAME", SQL_IDENTIFIER);           // not null
            addColumn(t, "COLUMN_SIZE", SqlType.SQL_INTEGER);
            addColumn(t, "BUFFER_LENGTH", SqlType.SQL_INTEGER);
            addColumn(t, "DECIMAL_DIGITS", SqlType.SQL_SMALLINT);
            addColumn(t, "PSEUDO_COLUMN", SqlType.SQL_SMALLINT);    // not null
            addColumn(t, "TABLE_CAT", SQL_IDENTIFIER);
            addColumn(t, "TABLE_SCHEM", SQL_IDENTIFIER);
            addColumn(t, "TABLE_NAME", SQL_IDENTIFIER);          // not null
            addColumn(t, "NULLABLE", SqlType.SQL_SMALLINT);         // not null
            addColumn(t, "IN_KEY", SqlType.SQL_BOOLEAN);            // not null

            // order: SCOPE
            // for unique:  TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME
            // false PK, as TABLE_CAT and/or TABLE_SCHEM may be null
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_SYSTEM_BESTROWIDENTIFIER].name, false,
                SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[] {
                0, 8, 9, 10, 1
            }, false);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);

        // calculated column values
        int scope;           // { temp, transaction, session }
        int pseudo;

        //-------------------------------------------
        // required for restriction of results via
        // DatabaseMetaData filter parameters, but
        // not actually required to be included in
        // DatabaseMetaData.getBestRowIdentifier()
        // result set
        //-------------------------------------------
        String  tableCatalog;    // table calalog
        String  tableSchema;     // table schema
        String  tableName;       // table name
        Boolean inKey;           // column participates in PK or AK?

        //-------------------------------------------

        /**
         * @todo -  Maybe include: - backing index (constraint) name?
         *       - column sequence in index (constraint)?
         */
        //-------------------------------------------
        // Intermediate holders
        Iterator       tables;
        Table          table;
        DITableInfo    ti;
        int[]          cols;
        Object[]       row;
        //HsqlProperties p;

        // Column number mappings
        int iscope          = 0;
        int icolumn_name    = 1;
        int idata_type      = 2;
        int itype_name      = 3;
        int icolumn_size    = 4;
        int ibuffer_length  = 5;
        int idecimal_digits = 6;
        int ipseudo_column  = 7;
        int itable_cat      = 8;
        int itable_schem    = 9;
        int itable_name     = 10;
        int inullable       = 11;
        int iinKey          = 12;

        // Initialization
        ti = new DITableInfo();
        tables =
            database.schemaManager.databaseObjectIterator(SchemaObjectTypes.TABLE);

        // Do it.
        while (tables.hasNext()) {
            table = (Table) tables.next();

            /** @todo - requires access to the actual columns */
            if (table.isView() || !isAccessibleTable(table)) {
                continue;
            }

            cols = table.getBestRowIdentifiers();

            if (cols == null) {
                continue;
            }

            ti.setTable(table);

            inKey = (table.isBestRowIdentifiersStrict());
            tableCatalog = table.getCatalogName().originalName;
            tableSchema = table.getSchemaName().originalName;
            tableName = table.getName().originalName;

            SqlType[] types = table.getColumnTypes();

            scope  = ti.getBRIScope();
            pseudo = ti.getBRIPseudo();

            for (int i = 0; i < cols.Length; i++) {
                ColumnSchema column = table.getColumn(i);

                row               = t.getEmptyRowData();
                row[iscope]       = scope;
                row[icolumn_name] = column.getName().name;
                row[idata_type] = (types[i].getAdoTypeCode());
                row[itype_name]   = types[i].getNameString();
                row[icolumn_size] =
                    (types[i].getAdoPrecision());
                row[ibuffer_length]  = null;
                row[idecimal_digits] = types[i].getAdoScale();
                row[ipseudo_column]  = pseudo;
                row[itable_cat]      = tableCatalog;
                row[itable_schem]    = tableSchema;
                row[itable_name]     = tableName;
                row[inullable] = (column.getNullability());
                row[iinKey]          = inKey;

                t.insertSys(store, row);
            }
        }

        return t;
    }

    /**
     * Retrieves a <code>Table</code> object describing the
     * visible columns of all accessible tables defined
     * within this database.<p>
     *
     * Each row is a column description with the following columns: <p>
     *
     * <pre class="SqlCodeExample">
     * TABLE_CAT         VARCHAR   table catalog
     * TABLE_SCHEM       VARCHAR   table schema
     * TABLE_NAME        VARCHAR   table name
     * COLUMN_NAME       VARCHAR   column name
     * DATA_TYPE         SMALLINT  SQL type from DITypes
     * TYPE_NAME         VARCHAR   canonical type name
     * COLUMN_SIZE       INTEGER   column size (length/precision)
     * BUFFER_LENGTH     INTEGER   transfer size in bytes, if definitely known
     * DECIMAL_DIGITS    INTEGER   # of fractional digits (scale)
     * NUM_PREC_RADIX    INTEGER   Radix
     * NULLABLE          INTEGER   is NULL allowed? (from DatabaseMetaData)
     * REMARKS           VARCHAR   comment describing column
     * COLUMN_DEF        VARCHAR   default value (possibly expression) for the
     *                             column, which should be interpreted as a
     *                             string when the value is enclosed in quotes
     *                             (may be <code>null</code>)
     * SQL_DATA_TYPE     VARCHAR   type code as expected in the SQL CLI SQLDA
     * SQL_DATETIME_SUB  INTEGER   the SQL CLI subtype for DATETIME types
     * CHAR_OCTET_LENGTH INTEGER   for char types, max # of chars/bytes in column
     * ORDINAL_POSITION  INTEGER   1-based index of column in table
     * IS_NULLABLE       VARCHAR   is column nullable? ("YES"|"NO"|""}
     * SCOPE_CATLOG      VARCHAR   catalog of REF attribute scope table
     * SCOPE_SCHEMA      VARCHAR   schema of REF attribute scope table
     * SCOPE_TABLE       VARCHAR   name of REF attribute scope table
     * SOURCE_DATA_TYPE  VARCHAR   source type of REF attribute
     * TYPE_SUB          INTEGER   HSQLDB data subtype code
     * </pre> <p>
     *
     * @return a <code>Table</code> object describing the
     *        visible columns of all accessible
     *        tables defined within this database.<p>
     */
    Table SYSTEM_COLUMNS() {

        Table t = sysTables[ID_SYSTEM_COLUMNS];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_SYSTEM_COLUMNS]);

            addColumn(t, "TABLE_CAT", SQL_IDENTIFIER);              // 0
            addColumn(t, "TABLE_SCHEM", SQL_IDENTIFIER);            // 1
            addColumn(t, "TABLE_NAME", SQL_IDENTIFIER);             // not null
            addColumn(t, "COLUMN_NAME", SQL_IDENTIFIER);            // not null
            addColumn(t, "DATA_TYPE", SqlType.SQL_SMALLINT);           // not null
            addColumn(t, "TYPE_NAME", SQL_IDENTIFIER);              // not null
            addColumn(t, "COLUMN_SIZE", SqlType.SQL_INTEGER);          // 6
            addColumn(t, "BUFFER_LENGTH", SqlType.SQL_INTEGER);        // 7
            addColumn(t, "DECIMAL_DIGITS", SqlType.SQL_INTEGER);       // 8
            addColumn(t, "NUM_PREC_RADIX", SqlType.SQL_INTEGER);       // 9
            addColumn(t, "NULLABLE", SqlType.SQL_INTEGER);             // not null
            addColumn(t, "REMARKS", CHARACTER_DATA);                // 11
            addColumn(t, "COLUMN_DEF", CHARACTER_DATA);             // 12
            addColumn(t, "SQL_DATA_TYPE", SqlType.SQL_INTEGER);        // 13
            addColumn(t, "SQL_DATETIME_SUB", SqlType.SQL_INTEGER);     // 14
            addColumn(t, "CHAR_OCTET_LENGTH", SqlType.SQL_INTEGER);    // 15
            addColumn(t, "ORDINAL_POSITION", SqlType.SQL_INTEGER);     // not null
            addColumn(t, "IS_NULLABLE", SqlType.SQL_BOOLEAN);                 // not null
            addColumn(t, "SCOPE_CATLOG", SQL_IDENTIFIER);           // 18
            addColumn(t, "SCOPE_SCHEMA", SQL_IDENTIFIER);           // 19
            addColumn(t, "SCOPE_TABLE", SQL_IDENTIFIER);            // 20
            addColumn(t, "SOURCE_DATA_TYPE", SQL_IDENTIFIER);       // 21

            // ----------------------------------------------------------------
            // JDBC 4.0 - added Mustang b86
            // ----------------------------------------------------------------
            addColumn(t, "IS_AUTOINCREMENT", SqlType.SQL_BOOLEAN);            // 22

            // ----------------------------------------------------------------
            // HSQLDB-specific
            // ----------------------------------------------------------------
            addColumn(t, "TYPE_SUB", SqlType.SQL_INTEGER);             // 23
            addColumn(t, "IS_PRIMARY_KEY", SqlType.SQL_BOOLEAN);       // 24

            // order: TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION
            // added for unique: TABLE_CAT
            // false PK, as TABLE_SCHEM and/or TABLE_CAT may be null
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_SYSTEM_COLUMNS].name, false,
                SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[] {
                0, 1, 2, 16
            }, false);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);

        // calculated column values
        String tableCatalog;
        String tableSchema;
        String tableName;

        // intermediate holders
        int         columnCount;
        Iterator    tables;
        Table       table;
        Object[]    row;
        DITableInfo ti;

        // column number mappings
        int itable_cat         = 0;
        int itable_schem       = 1;
        int itable_name        = 2;
        int icolumn_name       = 3;
        int idata_type         = 4;
        int itype_name         = 5;
        int icolumn_size       = 6;
        //int ibuffer_length     = 7;
        int idecimal_digits    = 8;
        int inum_prec_radix    = 9;
        int inullable          = 10;
        int iremark            = 11;
        int icolumn_def        = 12;
        int isql_data_type     = 13;
        int isql_datetime_sub  = 14;
        int ichar_octet_length = 15;
        int iordinal_position  = 16;
        int iis_nullable       = 17;
        //int iscope_cat         = 18;
        //int iscope_schem       = 19;
        //int iscope_table       = 20;
        //int isrc_data_type     = 21;

        // JDBC 4.0
        int iis_autoinc = 22;

        // HSQLDB-specific
        int itype_sub = 23;
        int iis_primary_key = 24;

        // Initialization
        tables = allTables();
        ti     = new DITableInfo();

        // Do it.
        while (tables.hasNext()) {
            table = (Table) tables.next();

            /** @todo - requires access to the actual columns */
            if (!isAccessibleTable(table)) {
                continue;
            }

            ti.setTable(table);

            tableCatalog = table.getCatalogName().originalName;
            tableSchema = table.getSchemaName().originalName;
            tableName = table.getName().originalName;
            columnCount  = table.getColumnCount();

            for (int i = 0; i < columnCount; i++) {
                ColumnSchema column = table.getColumn(i);
                SqlType         type   = column.getDataType();
                
                row = t.getEmptyRowData();

                //
                row[itable_cat]         = tableCatalog;
                row[itable_schem]       = tableSchema;
                row[itable_name]        = tableName;
                row[icolumn_name] = column.getName().originalName;
                row[idata_type] = (type.getAdoTypeCode());
                row[itype_name]         = type.getNameString();
                row[icolumn_size]       = 0;
                row[ichar_octet_length] = 0;

                if (type.isCharacterType()) {
                    row[icolumn_size] =
                        (type.getAdoPrecision());

                    // this is length not octet_length, for character columns
                    row[ichar_octet_length] =
                        (type.getAdoPrecision());
                }

                if (type.isBinaryType()) {
                    row[icolumn_size] =
                        (type.getAdoPrecision());
                    row[ichar_octet_length] =
                        (type.getAdoPrecision());
                }

                if (type.isNumberType()) {
                    row[icolumn_size] = (
                        ((NumberType) type).getNumericPrecisionInRadix());
                    row[inum_prec_radix] =
                        (type.getPrecisionRadix());

                    if (type.isExactNumberType()) {
                        row[idecimal_digits] = (long)(type.scale);
                    }
                }

                if (type.isDateTimeType()) {
                    int size = (int) column.getDataType().displaySize();

                    row[icolumn_size] = (size);
                }

                row[inum_prec_radix]   = ti.getColPrecRadix(i);
                row[inullable] = (column.getNullability());
                row[iremark]           = ti.getColRemarks(i);
                row[icolumn_def]       = column.getDefaultSQL();
                row[isql_data_type]    = (type.typeCode);
                row[isql_datetime_sub] = ti.getColSqlDateTimeSub(i);
                row[iordinal_position] = (i+1);
                row[iis_nullable] = column.getNullability() == SchemaObjectNullability.NO_NULLS 
                                                ? false : true;

                // JDBC 4.0
                row[iis_autoinc] = column.isIdentity() ;

                // HSQLDB-specific
                row[itype_sub] = ti.getColDataTypeSub(i);
                row[iis_primary_key] = column.isPrimaryKey();

                t.insertSys(store, row);
            }
        }

        return t;
    }

    /**
     * Retrieves a <code>Table</code> object describing, for each
     * accessible referencing and referenced table, how the referencing
     * tables import, for the purposes of referential integrity,
     * the columns of the referenced tables.<p>
     *
     * Each row is a foreign key column description with the following
     * columns: <p>
     *
     * <pre class="SqlCodeExample">
     * PKTABLE_CAT   VARCHAR   referenced table catalog
     * PKTABLE_SCHEM VARCHAR   referenced table schema
     * PKTABLE_NAME  VARCHAR   referenced table name
     * PKCOLUMN_NAME VARCHAR   referenced column name
     * FKTABLE_CAT   VARCHAR   referencing table catalog
     * FKTABLE_SCHEM VARCHAR   referencing table schema
     * FKTABLE_NAME  VARCHAR   referencing table name
     * FKCOLUMN_NAME VARCHAR   referencing column
     * KEY_SEQ       SMALLINT  sequence number within foreign key
     * UPDATE_RULE   SMALLINT
     *    { Cascade | Set Null | Set Default | Restrict (No Action)}?
     * DELETE_RULE   SMALLINT
     *    { Cascade | Set Null | Set Default | Restrict (No Action)}?
     * FK_NAME       VARCHAR   foreign key constraint name
     * PK_NAME       VARCHAR   primary key or unique constraint name
     * DEFERRABILITY SMALLINT
     *    { initially deferred | initially immediate | not deferrable }
     * </pre> <p>
     *
     * @return a <code>Table</code> object describing how accessible tables
     *      import other accessible tables' primary key and/or unique
     *      constraint columns
     */
    Table SYSTEM_CROSSREFERENCE() {

        Table t = sysTables[ID_SYSTEM_CROSSREFERENCE];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_SYSTEM_CROSSREFERENCE]);

            addColumn(t, "PKTABLE_CAT", SQL_IDENTIFIER);
            addColumn(t, "PKTABLE_SCHEM", SQL_IDENTIFIER);
            addColumn(t, "PKTABLE_NAME", SQL_IDENTIFIER);        // not null
            addColumn(t, "PKCOLUMN_NAME", SQL_IDENTIFIER);       // not null
            addColumn(t, "FKTABLE_CAT", SQL_IDENTIFIER);
            addColumn(t, "FKTABLE_SCHEM", SQL_IDENTIFIER);
            addColumn(t, "FKTABLE_NAME", SQL_IDENTIFIER);        // not null
            addColumn(t, "FKCOLUMN_NAME", SQL_IDENTIFIER);       // not null
            addColumn(t, "KEY_SEQ", SqlType.SQL_SMALLINT);          // not null
            addColumn(t, "UPDATE_RULE", SqlType.SQL_SMALLINT);      // not null
            addColumn(t, "DELETE_RULE", SqlType.SQL_SMALLINT);      // not null
            addColumn(t, "FK_NAME", SQL_IDENTIFIER);
            addColumn(t, "PK_NAME", SQL_IDENTIFIER);
            addColumn(t, "DEFERRABILITY", SqlType.SQL_SMALLINT);    // not null

            // order: FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ
            // added for unique: FK_NAME
            // false PK, as FKTABLE_CAT, FKTABLE_SCHEM and/or FK_NAME
            // may be null
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_SYSTEM_CROSSREFERENCE].name, false,
                SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[] {
                4, 5, 6, 8, 11
            }, false);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);

        // calculated column values
        String  pkTableCatalog;
        String  pkTableSchema;
        String  pkTableName;
        String  pkColumnName;
        String  fkTableCatalog;
        String  fkTableSchema;
        String  fkTableName;
        String  fkColumnName;
        int? keySequence;
        int? updateRule;
        int? deleteRule;
        String  fkName;
        String  pkName;
        int? deferrability;

        // Intermediate holders
        Iterator      tables;
        Table         table;
        Table         fkTable;
        Table         pkTable;
        int           columnCount;
        int[]         mainCols;
        int[]         refCols;
        Constraint[]  constraints;
        Constraint    constraint;
        int           constraintCount;
        EfzArrayList fkConstraintsList;
        Object[]      row;

        // column number mappings
        int ipk_table_cat   = 0;
        int ipk_table_schem = 1;
        int ipk_table_name  = 2;
        int ipk_column_name = 3;
        int ifk_table_cat   = 4;
        int ifk_table_schem = 5;
        int ifk_table_name  = 6;
        int ifk_column_name = 7;
        int ikey_seq        = 8;
        int iupdate_rule    = 9;
        int idelete_rule    = 10;
        int ifk_name        = 11;
        int ipk_name        = 12;
        int ideferrability  = 13;

        tables =
            database.schemaManager.databaseObjectIterator(SchemaObjectTypes.TABLE);

        // We must consider all the constraints in all the user tables, since
        // this is where reference relationships are recorded.  However, we
        // are only concerned with Constraint.FOREIGN_KEY constraints here
        // because their corresponing Constraint.MAIN entries are essentially
        // duplicate data recorded in the referenced rather than the
        // referencing table.  Also, we skip constraints where either
        // the referenced, referencing or both tables are not accessible
        // relative to the session of the calling context
        fkConstraintsList = new EfzArrayList();

        while (tables.hasNext()) {
            table = (Table) tables.next();

            if (table.isView() || !isAccessibleTable(table)) {
                continue;
            }

            constraints     = table.getConstraints();
            constraintCount = constraints.Length;

            for (int i = 0; i < constraintCount; i++) {
                constraint = (Constraint) constraints[i];

                if (constraint.getConstraintType() == SchemaObjectConstraintTypes
                        .FOREIGN_KEY && isAccessibleTable(constraint
                            .getRef())) {
                    fkConstraintsList.add(constraint);
                }
            }
        }

        // Now that we have all of the desired constraints, we need to
        // process them, generating one row in our ouput table for each
        // imported/exported column pair of each constraint.
        // Do it.
        for (int i = 0; i < fkConstraintsList.size(); i++) {
            constraint     = (Constraint) fkConstraintsList.get(i);
            pkTable        = constraint.getMain();
            pkTableName = pkTable.getName().originalName;
            fkTable        = constraint.getRef();
            fkTableName = fkTable.getName().originalName;
            pkTableCatalog = pkTable.getCatalogName().originalName;
            pkTableSchema = pkTable.getSchemaName().originalName;
            fkTableCatalog = fkTable.getCatalogName().originalName;
            fkTableSchema = fkTable.getSchemaName().originalName;
            mainCols       = constraint.getMainColumns();
            refCols        = constraint.getRefColumns();
            columnCount    = refCols.Length;
            fkName = constraint.getRefName().originalName;
            pkName = constraint.getMainName().originalName;
            deferrability  = (constraint.getDeferability());

            //pkName = constraint.getMainIndex().getName().name;
            deleteRule = (constraint.getDeleteAction());
            updateRule = (constraint.getUpdateAction());

            for (int j = 0; j < columnCount; j++) {
                keySequence          = (j + 1);
                pkColumnName = pkTable.getColumn(mainCols[j]).getNameString();
                fkColumnName = fkTable.getColumn(refCols[j]).getNameString();
                row                  = t.getEmptyRowData();
                row[ipk_table_cat]   = pkTableCatalog;
                row[ipk_table_schem] = pkTableSchema;
                row[ipk_table_name]  = pkTableName;
                row[ipk_column_name] = pkColumnName;
                row[ifk_table_cat]   = fkTableCatalog;
                row[ifk_table_schem] = fkTableSchema;
                row[ifk_table_name]  = fkTableName;
                row[ifk_column_name] = fkColumnName;
                row[ikey_seq]        = keySequence;
                row[iupdate_rule]    = updateRule;
                row[idelete_rule]    = deleteRule;
                row[ifk_name]        = fkName;
                row[ipk_name]        = pkName;
                row[ideferrability]  = deferrability;

                t.insertSys(store, row);
            }
        }

        return t;
    }

    /**
     * Retrieves a <code>Table</code> object describing the visible
     * <code>Index</code> objects for each accessible table defined
     * within this database.<p>
     *
     * Each row is an index column description with the following
     * columns: <p>
     *
     * <pre class="SqlCodeExample">
     * TABLE_CAT        VARCHAR   table's catalog
     * TABLE_SCHEM      VARCHAR   simple name of table's schema
     * TABLE_NAME       VARCHAR   simple name of the table using the index
     * NON_UNIQUE       BOOLEAN   can index values be non-unique?
     * INDEX_QUALIFIER  VARCHAR   catalog in which the index is defined
     * INDEX_NAME       VARCHAR   simple name of the index
     * TYPE             SMALLINT  index type: { Clustered | Hashed | Other }
     * ORDINAL_POSITION SMALLINT  column sequence number within index
     * COLUMN_NAME      VARCHAR   simple column name
     * ASC_OR_DESC      VARCHAR   col. sort sequence: {"A" (Asc) | "D" (Desc)}
     * CARDINALITY      INTEGER   # of unique values in index (not implemented)
     * PAGES            INTEGER   index page use (not implemented)
     * FILTER_CONDITION VARCHAR   filter condition, if any (not implemented)
     * // HSQLDB-extension
     * ROW_CARDINALITY  INTEGER   total # of rows in index (not implemented)
     * </pre> <p>
     *
     * @return a <code>Table</code> object describing the visible
     *        <code>Index</code> objects for each accessible
     *        table defined within this database.
     */
    Table SYSTEM_INDEXINFO() {

        Table t = sysTables[ID_SYSTEM_INDEXINFO];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_SYSTEM_INDEXINFO]);

            // JDBC
            addColumn(t, "TABLE_CAT", SQL_IDENTIFIER);
            addColumn(t, "TABLE_SCHEM", SQL_IDENTIFIER);
            addColumn(t, "TABLE_NAME", SQL_IDENTIFIER);             // NOT NULL
            addColumn(t, "NON_UNIQUE", SqlType.SQL_BOOLEAN);           // NOT NULL
            addColumn(t, "INDEX_QUALIFIER", SQL_IDENTIFIER);
            addColumn(t, "INDEX_NAME", SQL_IDENTIFIER);
            addColumn(t, "TYPE", SqlType.SQL_SMALLINT);                // NOT NULL
            addColumn(t, "ORDINAL_POSITION", SqlType.SQL_SMALLINT);    // NOT NULL
            addColumn(t, "COLUMN_NAME", SQL_IDENTIFIER);
            addColumn(t, "ASC_OR_DESC", CHARACTER_DATA);
            addColumn(t, "CARDINALITY", SqlType.SQL_INTEGER);
            addColumn(t, "PAGES", SqlType.SQL_INTEGER);
            addColumn(t, "FILTER_CONDITION", CHARACTER_DATA);
          

            // HSQLDB extension
            addColumn(t, "ROW_CARDINALITY", SqlType.SQL_INTEGER);

            addColumn(t, "UNIQUE_INDEX", SqlType.SQL_BOOLEAN);
            addColumn(t, "PRIMARY_INDEX", SqlType.SQL_BOOLEAN);

            // order: NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
            // added for unique: INDEX_QUALIFIER, TABLE_NAME
            // false PK, as INDEX_QUALIFIER may be null
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_SYSTEM_INDEXINFO].name, false,
                SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[] {
                0, 1, 2, 3, 4, 5, 7
            }, false);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);

        // calculated column values
        String  tableCatalog;
        String  tableSchema;
        String  tableName;
        Boolean nonUnique;
        String  indexQualifier;
        String  indexName;
        int? indexType;

        //Integer ordinalPosition;
        //String  columnName;
        //String  ascOrDesc;
        int? cardinality;
        int? pages;
        String  filterCondition;
        int? rowCardinality;
        Boolean uniqueIndex;
        Boolean primaryIndex;

        // Intermediate holders
        Iterator tables;
        Table    table;
        int      indexCount;
        int[]    cols;
        int      col;
        int      colCount;
        Object[] row;

        // column number mappings
        int itable_cat        = 0;
        int itable_schem      = 1;
        int itable_name       = 2;
        int inon_unique       = 3;
        int iindex_qualifier  = 4;
        int iindex_name       = 5;
        int itype             = 6;
        int iordinal_position = 7;
        int icolumn_name      = 8;
        int iasc_or_desc      = 9;
        int icardinality      = 10;
        int ipages            = 11;
        int ifilter_condition = 12;
        int irow_cardinality  = 13;
        int iunique_index     = 14;
        int iprimary_index    = 15;

        // Initialization
        tables =
            database.schemaManager.databaseObjectIterator(SchemaObjectTypes.TABLE);

        // Do it.
        while (tables.hasNext()) {
            table = (Table) tables.next();

            if (table.isView() || !isAccessibleTable(table)) {
                continue;
            }

            tableCatalog = table.getCatalogName().originalName;
            tableSchema = table.getSchemaName().originalName;
            tableName = table.getName().originalName;

            // not supported yet
            filterCondition = null;

            // different cat for index not supported yet
            indexQualifier = tableCatalog;
            indexCount     = table.getIndexCount();

            // process all of the visible indices for this table
            for (int i = 0; i < indexCount; i++) {
                Index index = table.getIndex(i);

                colCount = table.getIndex(i).getColumnCount();

                if (colCount < 1) {
                    continue;
                }

                indexName = index.getName().originalName;
                nonUnique      = index.isUnique() ? false
                                                  : true;
                cardinality    = null;
                pages          = 0;
                rowCardinality = null;
                cols           = index.getColumns();
                indexType      = (3);
                uniqueIndex         = !nonUnique;
                primaryIndex     = table.hasPrimaryKey() ? index == table.getPrimaryIndex() : false;

                for (int k = 0; k < colCount; k++) {
                    col                    = cols[k];
                    row                    = t.getEmptyRowData();
                    row[itable_cat]        = tableCatalog;
                    row[itable_schem]      = tableSchema;
                    row[itable_name]       = tableName;
                    row[inon_unique]       = nonUnique;
                    row[iindex_qualifier]  = indexQualifier;
                    row[iindex_name]       = indexName;
                    row[itype]             = indexType;
                    row[iordinal_position] = (k+1);
                    row[icolumn_name] =
                        table.getColumn(cols[k]).getName().name;
                    row[iasc_or_desc]      = "A";
                    row[icardinality]      = cardinality;
                    row[ipages]            = pages;
                    row[irow_cardinality]  = rowCardinality;
                    row[ifilter_condition] = filterCondition;
                    row[iunique_index]           = uniqueIndex;
                    row[iprimary_index]      = primaryIndex;

                    t.insertSys(store, row);
                }
            }
        }

        return t;
    }

    /**
     * Retrieves a <code>Table</code> object describing the visible
     * primary key columns of each accessible table defined within
     * this database. <p>
     *
     * Each row is a PRIMARY KEY column description with the following
     * columns: <p>
     *
     * <pre class="SqlCodeExample">
     * TABLE_CAT   VARCHAR   table catalog
     * TABLE_SCHEM VARCHAR   table schema
     * TABLE_NAME  VARCHAR   table name
     * COLUMN_NAME VARCHAR   column name
     * KEY_SEQ     SMALLINT  sequence number within primary key
     * PK_NAME     VARCHAR   primary key constraint name
     * </pre> <p>
     *
     * @return a <code>Table</code> object describing the visible
     *        primary key columns of each accessible table
     *        defined within this database.
     */
    Table SYSTEM_PRIMARYKEYS() {

        Table t = sysTables[ID_SYSTEM_PRIMARYKEYS];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_SYSTEM_PRIMARYKEYS]);

            addColumn(t, "TABLE_CAT", SQL_IDENTIFIER);
            addColumn(t, "TABLE_SCHEM", SQL_IDENTIFIER);
            addColumn(t, "TABLE_NAME", SQL_IDENTIFIER);     // not null
            addColumn(t, "COLUMN_NAME", SQL_IDENTIFIER);    // not null
            addColumn(t, "KEY_SEQ", SqlType.SQL_SMALLINT);     // not null
            addColumn(t, "PK_NAME", SQL_IDENTIFIER);

            // order: COLUMN_NAME
            // added for unique: TABLE_NAME, TABLE_SCHEM, TABLE_CAT
            // false PK, as  TABLE_SCHEM and/or TABLE_CAT may be null
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_SYSTEM_PRIMARYKEYS].name, false,
                SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[] {
                3, 2, 1, 0
            }, false);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);

        // calculated column values
        String tableCatalog;
        String tableSchema;
        String tableName;

        //String  columnName;
        //Integer keySequence;
        String primaryKeyName;

        // Intermediate holders
        Iterator       tables;
        Table          table;
        Object[]       row;
        Constraint     constraint;
        int[]          cols;
        int            colCount;
        EffiProzProperties p;

        // column number mappings
        int itable_cat   = 0;
        int itable_schem = 1;
        int itable_name  = 2;
        int icolumn_name = 3;
        int ikey_seq     = 4;
        int ipk_name     = 5;

        // Initialization
        p = database.getProperties();
        tables =
            database.schemaManager.databaseObjectIterator(SchemaObjectTypes.TABLE);

        while (tables.hasNext()) {
            table = (Table) tables.next();

            if (table.isView() || !isAccessibleTable(table)
                    || !table.hasPrimaryKey()) {
                continue;
            }

            constraint     = table.getPrimaryConstraint();
            tableCatalog = table.getCatalogName().originalName;
            tableSchema = table.getSchemaName().originalName;
            tableName = table.getName().originalName;
            primaryKeyName = constraint.getName().originalName;
            cols           = constraint.getMainColumns();
            colCount       = cols.Length;

            for (int j = 0; j < colCount; j++) {
                row               = t.getEmptyRowData();
                row[itable_cat]   = tableCatalog;
                row[itable_schem] = tableSchema;
                row[itable_name]  = tableName;
                row[icolumn_name] = table.getColumn(cols[j]).getName().name;
                row[ikey_seq]     = (j);
                row[ipk_name]     = primaryKeyName;

                t.insertSys(store, row);
            }
        }

        return t;
    }

    /**
     * getClientInfoProperties
     *
     * @return Result
     *
     * <li><b>NAME</b> String=> The name of the client info property<br>
     * <li><b>MAX_LEN</b> int=> The maximum length of the value for the property<br>
     * <li><b>DEFAULT_VALUE</b> String=> The default value of the property<br>
     * <li><b>DESCRIPTION</b> String=> A description of the property.  This will typically
     *                                                  contain information as to where this property is
     *                                                  stored in the database.
     */
    Table SYSTEM_CONNECTION_PROPERTIES() {

        Table t = sysTables[ID_SYSTEM_CONNECTION_PROPERTIES];

        if (t == null) {
            t = createBlankTable(
                sysTableHsqlNames[ID_SYSTEM_CONNECTION_PROPERTIES]);

            addColumn(t, "NAME", SQL_IDENTIFIER);
            addColumn(t, "MAX_LEN", SqlType.SQL_INTEGER);
            addColumn(t, "DEFAULT_VALUE", SQL_IDENTIFIER);    // not null
            addColumn(t, "DESCRIPTION", SQL_IDENTIFIER);      // not null

            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_SYSTEM_CONNECTION_PROPERTIES].name, false,
                SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[]{ 0 }, true);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);
        Object[]        row;

        // column number mappings
        int iname          = 0;
        int imax_len       = 1;
        int idefault_value = 2;
        int idescription   = 3;
        IEnumerable<object[]>  it = EffiProzDatabaseProperties.getPropertiesMetaIterator();

        foreach (var meta in it) {           
            int propType =
                Convert.ToInt32(meta[EffiProzDatabaseProperties.indexType]);

            if (propType == EffiProzDatabaseProperties.FILE_PROPERTY) {
                if (EffiProzDatabaseProperties.effiproz_readonly.Equals(
                        meta[EffiProzDatabaseProperties.indexName]) || EffiProzDatabaseProperties
                            .effiproz_files_readonly.Equals(
                                meta[EffiProzDatabaseProperties.indexName])) { }
                else {
                    continue;
                }
            } else if (propType != EffiProzDatabaseProperties.SQL_PROPERTY) {
                continue;
            }

            row = t.getEmptyRowData();

            Object def = meta[EffiProzDatabaseProperties.indexDefaultValue];

            row[iname] = meta[EffiProzDatabaseProperties.indexName];
            row[imax_len]       = (8);
            row[idefault_value] = def == null ? null
                                              : def.ToString();
            row[idescription]   = "see HyperSQL guide";

            t.insertSys(store, row);
        }

        return t;
    }

    /**
     * Inserts a set of procedure description rows into the <code>Table</code>
     * object specified by the <code>t</code> argument. <p>
     *
     * @param t the table into which the specified rows will eventually
     *      be inserted
     * @param l the list of procedure name aliases to which the specified column
     *      values apply
     * @param cat the procedure catalog name
     * @param schem the procedure schema name
     * @param pName the base (non-alias) procedure name
     * @param ip the procedure input parameter count
     * @param op the procedure output parameter count
     * @param rs the procedure result column count
     * @param remark a human-readable remark regarding the procedure
     * @param pType the procedure type code, indicating whether it is a
     *      function, procedure, or uncatagorized (i.e. returns
     *      a value, does not return a value, or it is unknown
     *      if it returns a value)
     * @param specificName the specific name of the procedure
     *      (typically but not limited to a
     *      fully qualified Java Method name and signature)
     * @param origin origin of the procedure, e.g.
     *      (["BUILTIN" | "USER DEFINED"] "ROUTINE" | "TRIGGER") | "ALIAS", etc.
     *
     */
    protected void addProcRows(Table t, EfzArrayList l, String cat,
                               String schem, String pName, int? ip,
                               int? op, int? rs, String remark,
                               int? pType, String specificName,
                               String origin) {

        PersistentStore store = database.persistentStoreCollection.getStore(t);

        // column number mappings
        int icat          = 0;
        int ischem        = 1;
        int ipname        = 2;
        int iinput_parms  = 3;
        int ioutput_parms = 4;
        int iresult_sets  = 5;
        int iremark       = 6;
        int iptype        = 7;
        int isn           = 8;
        int iporigin      = 9;
        Object[]  row           = t.getEmptyRowData();

        row[icat]          = cat;
        row[ischem]        = schem;
        row[ipname]        = pName;
        row[iinput_parms]  = ip;
        row[ioutput_parms] = op;
        row[iresult_sets]  = rs;
        row[iremark]       = remark;
        row[iptype]        = pType;
        row[iporigin]      = origin;
        row[isn]           = specificName;

        t.insertSys(store, row);

        if (l != null) {
            int size = l.size();

            for (int i = 0; i < size; i++) {
                row                = t.getEmptyRowData();
                pName              = (String) l.get(i);
                row[icat]          = cat;
                row[ischem]        = schem;
                row[ipname]        = pName;
                row[iinput_parms]  = ip;
                row[ioutput_parms] = op;
                row[iresult_sets]  = rs;
                row[iremark]       = remark;
                row[iptype]        = pType;
                row[iporigin]      = "ALIAS";
                row[isn]           = specificName;

                t.insertSys(store, row);
            }
        }
    }

    /**
     * Inserts a set of procedure column description rows into the
     * <code>Table</code> specified by the <code>t</code> argument.
     *
     * <p>
     *
     * @param t the table in which the rows are to be inserted
     * @param l the list of procedure name aliases to which the specified
     *   column values apply
     * @param cat the procedure's catalog name
     * @param schem the procedure's schema name
     * @param pName the procedure's simple base (non-alias) name
     * @param cName the procedure column name
     * @param cType the column type (return, parameter, result)
     * @param dType the column's data type code
     * @param tName the column's canonical data type name
     * @param prec the column's precision
     * @param len the column's buffer length
     * @param scale the column's scale (decimal digits)
     * @param radix the column's numeric precision radix
     * @param nullability the column's java.sql.DatbaseMetaData nullabiliy code
     * @param remark a human-readable remark regarding the column
     * @param colDefault String
     * @param sqlDataType helper value to back JDBC contract sort order
     * @param sqlDateTimeSub Integer
     * @param charOctetLength Integer
     * @param ordinalPosition Integer
     * @param isNullable String
     * @param specificName the specific name of the procedure (typically but
     *   not limited to a fully qualified Java Method name and signature)
     * @param jdbcSequence int
     */
    protected void addPColRows(Table t, EfzArrayList l, String cat,
                               String schem, String pName, String cName,
                               int? cType, int? dType, String tName,
                               int? prec, int? len, int? scale,
                               int? radix, int? nullability,
                               String remark, String colDefault,
                               int? sqlDataType, int? sqlDateTimeSub,
                               int? charOctetLength,
                               int? ordinalPosition, String isNullable,
                               String specificName) {

        PersistentStore store = database.persistentStoreCollection.getStore(t);

        // column number mappings
        int icat       = 0;
        int ischem     = 1;
        int iname      = 2;
        int icol_name  = 3;
        int icol_type  = 4;
        int idata_type = 5;
        int itype_name = 6;
        int iprec      = 7;
        int ilength    = 8;
        int iscale     = 9;
        int iradix     = 10;
        int inullable  = 11;
        int iremark    = 12;

        // JDBC 4.0
        int icol_default      = 13;
        int isql_data_type    = 14;
        int isql_datetime_sub = 15;
        int ichar_octet_len   = 16;
        int iordinal_position = 17;
        int iis_nullable      = 18;
        int ispecific_name    = 19;

        // initialization
        Object[] row = t.getEmptyRowData();

        // Do it.
        row[icat]       = cat;
        row[ischem]     = schem;
        row[iname]      = pName;
        row[icol_name]  = cName;
        row[icol_type]  = cType;
        row[idata_type] = dType;
        row[itype_name] = tName;
        row[iprec]      = prec;
        row[ilength]    = len;
        row[iscale]     = scale;
        row[iradix]     = radix;
        row[inullable]  = nullability;
        row[iremark]    = remark;

        // JDBC 4.0
        row[icol_default]      = colDefault;
        row[isql_data_type]    = sqlDataType;
        row[isql_datetime_sub] = sqlDateTimeSub;
        row[ichar_octet_len]   = charOctetLength;
        row[iordinal_position] = ordinalPosition;
        row[iis_nullable]      = isNullable;
        row[ispecific_name]    = specificName;

        t.insertSys(store, row);

        if (l != null) {
            int size = l.size();

            for (int i = 0; i < size; i++) {
                row             = t.getEmptyRowData();
                pName           = (String) l.get(i);
                row[icat]       = cat;
                row[ischem]     = schem;
                row[iname]      = pName;
                row[icol_name]  = cName;
                row[icol_type]  = cType;
                row[idata_type] = dType;
                row[itype_name] = tName;
                row[iprec]      = prec;
                row[ilength]    = len;
                row[iscale]     = scale;
                row[iradix]     = radix;
                row[inullable]  = nullability;
                row[iremark]    = remark;

                // JDBC 4.0
                row[icol_default]      = colDefault;
                row[isql_data_type]    = sqlDataType;
                row[isql_datetime_sub] = sqlDateTimeSub;
                row[ichar_octet_len]   = charOctetLength;
                row[iordinal_position] = ordinalPosition;
                row[iis_nullable]      = isNullable;
                row[ispecific_name]    = specificName;

                t.insertSys(store, row);
            }
        }
    }

    /**
     * Retrieves a <code>Table</code> object describing the accessible schemas
     * defined within this database. <p>
     *
     * Each row is a schema description with the following
     * columns: <p>
     *
     * <pre class="SqlCodeExample">
     * TABLE_SCHEM      VARCHAR   simple schema name
     * TABLE_CATALOG    VARCHAR   catalog in which schema is defined
     * IS_DEFAULT       BOOLEAN   is the schema the default for new sessions
     * </pre> <p>
     *
     * @return table containing information about schemas defined
     *      within this database
     */
    Table SYSTEM_SCHEMAS() {

        Table t = sysTables[ID_SYSTEM_SCHEMAS];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_SYSTEM_SCHEMAS]);

            addColumn(t, "TABLE_SCHEM", SQL_IDENTIFIER);    // not null
            addColumn(t, "TABLE_CATALOG", SQL_IDENTIFIER);
            addColumn(t, "IS_DEFAULT", SqlType.SQL_BOOLEAN);

            // order: TABLE_SCHEM
            // true PK, as rows never have null TABLE_SCHEM
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_SYSTEM_SCHEMAS].name, false,
                SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[]{ 0 }, true);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);
        Iterator        schemas;
        Object[]        row;

        // Initialization
        schemas = database.schemaManager.fullSchemaNamesIterator();

        String defschema =
            database.schemaManager.getDefaultSchemaHsqlName().name;

        // Do it.
        while (schemas.hasNext()) {
            row = t.getEmptyRowData();

            String schema = (String) schemas.next();

            row[0] = schema;
            row[1] = database.getCatalogName().name;
            row[2] = schema.Equals(defschema) ? true
                                              : false;

            t.insertSys(store, row);
        }

        return t;
    }

    Table SYSTEM_CATALOGS()
    {
        Table t = sysTables[ID_SYSTEM_CATALOGS];

        if (t == null)
        {
            t = createBlankTable(sysTableHsqlNames[ID_SYSTEM_CATALOGS]);

            addColumn(t, "SYSTEM_CATALOG_NAME", SQL_IDENTIFIER);

            // order: TABLE_SCHEM
            // true PK, as rows never have null TABLE_SCHEM
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_SYSTEM_CATALOGS].name, false,
                SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[] { 0 }, true);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);
        Object[] row;

        row = t.getEmptyRowData();
        row[0] = "PUBLIC";
        t.insertSys(store, row);

        return t;
    }

    /**
     * Retrieves a <code>Table</code> object describing the accessible
     * tables defined within this database. <p>
     *
     * Each row is a table description with the following columns: <p>
     *
     * <pre class="SqlCodeExample">
     * TABLE_CAT                 VARCHAR   table catalog
     * TABLE_SCHEM               VARCHAR   table schema
     * TABLE_NAME                VARCHAR   table name
     * TABLE_TYPE                VARCHAR   {"TABLE" | "VIEW" |
     *                                      "SYSTEM TABLE" | "GLOBAL TEMPORARY"}
     * REMARKS                   VARCHAR   comment on the table.
     * TYPE_CAT                  VARCHAR   table type catalog (not implemented).
     * TYPE_SCHEM                VARCHAR   table type schema (not implemented).
     * TYPE_NAME                 VARCHAR   table type name (not implemented).
     * SELF_REFERENCING_COL_NAME VARCHAR   designated "identifier" column of
     *                                     typed table (not implemented).
     * REF_GENERATION            VARCHAR   {"SYSTEM" | "USER" |
     *                                      "DERIVED" | NULL } (not implemented)
     * HSQLDB_TYPE               VARCHAR   HSQLDB-specific type:
     *                                     {"MEMORY" | "CACHED" | "TEXT" | ...}
     * READ_ONLY                 BOOLEAN   TRUE if table is read-only,
     *                                     else FALSE.
     * COMMIT_ACTION             VARCHAR   "PRESERVE" or "DELETE" for temp tables,
     *                                     else NULL
     * </pre> <p>
     *
     * @return a <code>Table</code> object describing the accessible
     *      tables defined within this database
     */
    Table SYSTEM_TABLES() {

        Table t = sysTables[ID_SYSTEM_TABLES];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_SYSTEM_TABLES]);

            // -------------------------------------------------------------
            // required
            // -------------------------------------------------------------
            addColumn(t, "TABLE_CAT", SQL_IDENTIFIER);
            addColumn(t, "TABLE_SCHEM", SQL_IDENTIFIER);
            addColumn(t, "TABLE_NAME", SQL_IDENTIFIER);       // not null
            addColumn(t, "TABLE_TYPE", CHARACTER_DATA);       // not null
            addColumn(t, "REMARKS", CHARACTER_DATA);

            // -------------------------------------------------------------
            // JDBC 3.0
            // -------------------------------------------------------------
            addColumn(t, "TYPE_CAT", SQL_IDENTIFIER);
            addColumn(t, "TYPE_SCHEM", SQL_IDENTIFIER);
            addColumn(t, "TYPE_NAME", SQL_IDENTIFIER);
            addColumn(t, "SELF_REFERENCING_COL_NAME", SQL_IDENTIFIER);
            addColumn(t, "REF_GENERATION", CHARACTER_DATA);

            // -------------------------------------------------------------
            // extended
            // ------------------------------------------------------------
            addColumn(t, "HSQLDB_TYPE", SQL_IDENTIFIER);
            addColumn(t, "READ_ONLY", SqlType.SQL_BOOLEAN);      // not null
            addColumn(t, "COMMIT_ACTION", CHARACTER_DATA);    // not null

            // ------------------------------------------------------------
            // order TABLE_TYPE, TABLE_SCHEM and TABLE_NAME
            // added for unique: TABLE_CAT
            // false PK, as TABLE_SCHEM and/or TABLE_CAT may be null
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_SYSTEM_TABLES].name, false,
                SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[] {
                3, 1, 2, 0
            }, false);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);

        // intermediate holders
        Iterator    tables;
        Table       table;
        Object[]    row;
        //QNameManager.QName    accessKey;
        DITableInfo ti;

        // column number mappings
        // JDBC 1
        int itable_cat   = 0;
        int itable_schem = 1;
        int itable_name  = 2;
        int itable_type  = 3;
        int iremark      = 4;

        // JDBC 3.0
        //int itype_cat   = 5;
        //int itype_schem = 6;
        //int itype_name  = 7;
        //int isref_cname = 8;
        //int iref_gen    = 9;

        // hsqldb ext
        int ihsqldb_type   = 10;
        int iread_only     = 11;
        int icommit_action = 12;

        // Initialization
        tables = allTables();
        ti     = new DITableInfo();

        // Do it.
        while (tables.hasNext()) {
            table = (Table) tables.next();

            if (!isAccessibleTable(table)) {
                continue;
            }

            ti.setTable(table);

            row               = t.getEmptyRowData();
            row[itable_cat]   = database.getCatalogName().originalName;
            row[itable_schem] = table.getSchemaName().originalName;
            row[itable_name]  = table.getName().originalName;
            row[itable_type]  = ti.getJDBCStandardType();
            row[iremark]      = ti.getRemark();
            row[ihsqldb_type] = ti.getHsqlType();
            row[iread_only]   = ti.isReadOnly();
            row[icommit_action] = table.isTemp()
                                  ? (table.onCommitPreserve() ? "PRESERVE"
                                                              : "DELETE")
                                  : null;

            t.insertSys(store, row);
        }

        return t;
    }

    /**
     * Retrieves a <code>Table</code> object describing the table types
     * available in this database. <p>
     *
     * In general, the range of values that may be commonly encounted across
     * most DBMS implementations is: <p>
     *
     * <UL>
     *   <LI><FONT color='#FF00FF'>"TABLE"</FONT>
     *   <LI><FONT color='#FF00FF'>"VIEW"</FONT>
     *   <LI><FONT color='#FF00FF'>"SYSTEM TABLE"</FONT>
     *   <LI><FONT color='#FF00FF'>"GLOBAL TEMPORARY"</FONT>
     *   <LI><FONT color='#FF00FF'>"LOCAL TEMPORARY"</FONT>
     *   <LI><FONT color='#FF00FF'>"ALIAS"</FONT>
     *   <LI><FONT color='#FF00FF'>"SYNONYM"</FONT>
     * </UL> <p>
     *
     * As of HSQLDB 1.7.2, the engine supports and thus this method reports
     * only a subset of the range above: <p>
     *
     * <UL>
     *   <LI><FONT color='#FF00FF'>"TABLE"</FONT>
     *    (HSQLDB MEMORY, CACHED and TEXT tables)
     *   <LI><FONT color='#FF00FF'>"VIEW"</FONT>  (Views)
     *   <LI><FONT color='#FF00FF'>"SYSTEM TABLE"</FONT>
     *    (The tables generated by this object)
     *   <LI><FONT color='#FF00FF'>"GLOBAL TEMPORARY"</FONT>
     *    (HSQLDB TEMP and TEMP TEXT tables)
     * </UL> <p>
     *
     * @return a <code>Table</code> object describing the table types
     *        available in this database
     */
    Table SYSTEM_TABLETYPES() {

        Table t = sysTables[ID_SYSTEM_TABLETYPES];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_SYSTEM_TABLETYPES]);

            addColumn(t, "TABLE_TYPE", SQL_IDENTIFIER);    // not null

            // order: TABLE_TYPE
            // true PK
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_SYSTEM_TABLETYPES].name, false,
                SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[]{ 0 }, true);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);
        Object[]        row;

        for (int i = 0; i < tableTypes.Length; i++) {
            row    = t.getEmptyRowData();
            row[0] = tableTypes[i];

            t.insertSys(store, row);
        }

        return t;
    }

    /**
     * Retrieves a <code>Table</code> object describing the
     * result expected by the JDBC DatabaseMetaData interface implementation
     * for system-defined SQL types supported as table columns.
     *
     * <pre class="SqlCodeExample">
     * TYPE_NAME          VARCHAR   the canonical name for DDL statements.
     * DATA_TYPE          SMALLINT  data type code from DITypes.
     * PRECISION          INTEGER   max column size.
     *                              number => max precision.
     *                              character => max characters.
     *                              datetime => max chars incl. frac. component.
     * LITERAL_PREFIX     VARCHAR   char(s) prefixing literal of this type.
     * LITERAL_SUFFIX     VARCHAR   char(s) terminating literal of this type.
     * CREATE_PARAMS      VARCHAR   Localized syntax-order list of domain
     *                              create parameter keywords.
     *                              - for human consumption only
     * NULLABLE           SMALLINT  {No Nulls | Nullable | Unknown}
     * CASE_SENSITIVE     BOOLEAN   case-sensitive in collations/comparisons?
     * SEARCHABLE         SMALLINT  {None | Char (Only WHERE .. LIKE) |
     *                               Basic (Except WHERE .. LIKE) |
     *                               Searchable (All forms)}
     * UNSIGNED_ATTRIBUTE BOOLEAN   {TRUE  (unsigned) | FALSE (signed) |
     *                               NULL (non-numeric or not applicable)}
     * FIXED_PREC_SCALE   BOOLEAN   {TRUE (fixed) | FALSE (variable) |
     *                               NULL (non-numeric or not applicable)}
     * AUTO_INCREMENT     BOOLEAN   automatic unique value generated for
     *                              inserts and updates when no value or
     *                              NULL specified?
     * LOCAL_TYPE_NAME    VARCHAR   localized name of data type;
     *                              - NULL if not supported.
     *                              - for human consuption only
     * MINIMUM_SCALE      SMALLINT  minimum scale supported.
     * MAXIMUM_SCALE      SMALLINT  maximum scale supported.
     * SQL_DATA_TYPE      INTEGER   value expected in SQL CLI SQL_DESC_TYPE
     *                              field of the SQLDA.
     * SQL_DATETIME_SUB   INTEGER   SQL CLI datetime/interval subcode.
     * NUM_PREC_RADIX     INTEGER   numeric base w.r.t # of digits reported in
     *                              PRECISION column (typically 10).
     * </pre> <p>
     *
     * @return a <code>Table</code> object describing the
     *      system-defined SQL types supported as table columns
     */
    Table SYSTEM_TYPEINFO() {

        Table t = sysTables[ID_SYSTEM_TYPEINFO];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_SYSTEM_TYPEINFO]);

            //-------------------------------------------
            // required by JDBC:
            // ------------------------------------------
            addColumn(t, "TYPE_NAME", SQL_IDENTIFIER);
            addColumn(t, "DATA_TYPE", SqlType.SQL_SMALLINT);
            addColumn(t, "PRECISION", SqlType.SQL_INTEGER);
            addColumn(t, "LITERAL_PREFIX", CHARACTER_DATA);
            addColumn(t, "LITERAL_SUFFIX", CHARACTER_DATA);
            addColumn(t, "CREATE_PARAMS", CHARACTER_DATA);
            addColumn(t, "NULLABLE", SqlType.SQL_SMALLINT);
            addColumn(t, "CASE_SENSITIVE", SqlType.SQL_BOOLEAN);
            addColumn(t, "SEARCHABLE", SqlType.SQL_INTEGER);
            addColumn(t, "UNSIGNED_ATTRIBUTE", SqlType.SQL_BOOLEAN);
            addColumn(t, "FIXED_PREC_SCALE", SqlType.SQL_BOOLEAN);
            addColumn(t, "AUTO_INCREMENT", SqlType.SQL_BOOLEAN);
            addColumn(t, "LOCAL_TYPE_NAME", SQL_IDENTIFIER);
            addColumn(t, "MINIMUM_SCALE", SqlType.SQL_SMALLINT);
            addColumn(t, "MAXIMUM_SCALE", SqlType.SQL_SMALLINT);
            addColumn(t, "SQL_DATA_TYPE", SqlType.SQL_INTEGER);
            addColumn(t, "SQL_DATETIME_SUB", SqlType.SQL_INTEGER);
            addColumn(t, "NUM_PREC_RADIX", SqlType.SQL_INTEGER);

            //-------------------------------------------
            // SQL CLI / ODBC - not in JDBC spec
            // ------------------------------------------
            addColumn(t, "INTERVAL_PRECISION", SqlType.SQL_INTEGER);
         

            // order:  DATA_TYPE, TYPE_NAME
            // true primary key
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_SYSTEM_TYPEINFO].name, false,
                SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[] {
                1, 0
            }, true);

            return t;
        }

        //-----------------------------------------
        // Same as SYSTEM_TYPEINFO
        //-----------------------------------------
        int itype_name          = 0;
        int idata_type          = 1;
        int iprecision          = 2;
        int iliteral_prefix     = 3;
        int iliteral_suffix     = 4;
        int icreate_params      = 5;
        int inullable           = 6;
        int icase_sensitive     = 7;
        int isearchable         = 8;
        int iunsigned_attribute = 9;
        int ifixed_prec_scale   = 10;
        int iauto_increment     = 11;
        int ilocal_type_name    = 12;
        int iminimum_scale      = 13;
        int imaximum_scale      = 14;
        int isql_data_type      = 15;
        int isql_datetime_sub   = 16;
        int inum_prec_radix     = 17;
        

        //------------------------------------------
        // Extensions
        //------------------------------------------
        // not in JDBC, but in SQL CLI SQLDA / ODBC
        //------------------------------------------
        int       iinterval_precision = 18;
     
        PersistentStore store = database.persistentStoreCollection.getStore(t);
        Object[]        row;
        DITypeInfo      ti;

        ti = new DITypeInfo();

       
        foreach (var typeName in SqlType.typeNames.Keys) {
            
            int    typeCode = SqlType.typeNames[typeName];
            SqlType   type     = SqlType.getDefaultType(typeCode);

            if (type == null) {
                continue;
            }

            row             = t.getEmptyRowData();
            row[itype_name] = typeName;
            row[idata_type] = (type.typeCode);

            long maxPrecision = type.getMaxPrecision();

            row[iprecision] = maxPrecision > int.MaxValue
                              ? int.MaxValue
                              : ((int) maxPrecision);

            if (type.isBinaryType() || type.isCharacterType()
                    || type.isDateTimeType() || type.isIntervalType()) {
                row[iliteral_prefix] = "\'";
                row[iliteral_suffix] = "\'";
            }

            if (type.acceptsPrecision() && type.acceptsScale()) {
                row[icreate_params] = "PRECISION,SCALE";
            } else if (type.acceptsPrecision()) {
                row[icreate_params] = type.isNumberType() ? "PRECISION"
                                                          : "LENGTH";
            } else if (type.acceptsScale()) {
                row[icreate_params] = "SCALE";
            }

            row[inullable] = 1;
            row[icase_sensitive] =
                type.isCharacterType()
                && type.typeCode != Types.VARCHAR_IGNORECASE ? true
                                                             : false;

            if (type.isLobType()) {
                row[isearchable] = 0;
            } else if (type.isCharacterType()
                       || (type.isBinaryType() && !type.isBitType())) {
                row[isearchable] = (3);
            } else {
                row[isearchable] = (2);
            }

            row[iunsigned_attribute] = false;
            row[ifixed_prec_scale] =
                type.typeCode == Types.SQL_NUMERIC
                || type.typeCode == Types.SQL_DECIMAL ? true
                                                      : false;
            row[iauto_increment]   = type.isIntegralType() ? true
                                                           : false;
            row[ilocal_type_name]  = type.getCSharpClassName();
            row[iminimum_scale]    = 0;
            row[imaximum_scale]    = (type.getMaxScale());
            row[isql_data_type]    = type.getAdoTypeCode();
            row[isql_datetime_sub] = null;
            row[inum_prec_radix] = (type.getPrecisionRadix());

            //------------------------------------------
            row[iinterval_precision] = ti.getIntervalPrecision();
           

            t.insertSys(store, row);
        }

        row             = t.getEmptyRowData();
        row[itype_name] = "DISTINCT";
        row[idata_type] = (Types.DISTINCT);

        return t;
    }

    /**
     * Retrieves a <code>Table</code> object describing the
     * visible <code>Users</code> defined within this database.
     * @return table containing information about the users defined within
     *      this database
     */
    Table SYSTEM_USERS() {

        Table t = sysTables[ID_SYSTEM_USERS];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_SYSTEM_USERS]);

            addColumn(t, "USER_NAME", SQL_IDENTIFIER);
            addColumn(t, "ADMIN", SqlType.SQL_BOOLEAN);
            addColumn(t, "INITIAL_SCHEMA", SQL_IDENTIFIER);

            // order: USER
            // true PK
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_SYSTEM_USERS].name, false,
                SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[]{ 0 }, true);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);

        // Intermediate holders
        EfzArrayList users;
        User          user;
        Object[]      row;
        QNameManager.QName      initialSchema;

        // Initialization
        users = database.getUserManager().listVisibleUsers(session);

        // Do it.
        for (int i = 0; i < users.size(); i++) {
            row           = t.getEmptyRowData();
            user          = (User) users.get(i);
            initialSchema = user.getInitialSchema();
            row[0]        = user.getNameString();
            row[1]        = (user.isAdmin());
            row[2]        = ((initialSchema == null) ? null
                                                     : initialSchema.name);

            t.insertSys(store, row);
        }

        return t;
    }

// -----------------------------------------------------------------------------
// SQL SCHEMATA VIEWS
// limited to views used in JDBC DatabaseMetaData

    /**
     * Retrieves a <code>Table</code> object describing the visible
     * access rights for all visible columns of all accessible
     * tables defined within this database.<p>
     *
     * Each row is a column privilege description with the following
     * columns: <p>
     *
     * <pre class="SqlCodeExample">
     * TABLE_CAT    VARCHAR   table catalog
     * TABLE_SCHEM  VARCHAR   table schema
     * TABLE_NAME   VARCHAR   table name
     * COLUMN_NAME  VARCHAR   column name
     * GRANTOR      VARCHAR   grantor of access
     * GRANTEE      VARCHAR   grantee of access
     * PRIVILEGE    VARCHAR   name of access
     * IS_GRANTABLE VARCHAR   grantable?: "YES" - grant to others, else "NO"
     * </pre>
     *
     * <b>Note:</b> From 1.9.0, HSQLDB supports column level
     * privileges. <p>
     *
     * @return a <code>Table</code> object describing the visible
     *        access rights for all visible columns of
     *        all accessible tables defined within this
     *        database
     */
    Table COLUMN_PRIVILEGES() {

        Table t = sysTables[ID_COLUMN_PRIVILEGES];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_COLUMN_PRIVILEGES]);

            addColumn(t, "GRANTOR", SQL_IDENTIFIER);           // not null
            addColumn(t, "GRANTEE", SQL_IDENTIFIER);           // not null
            addColumn(t, "TABLE_CATALOG", SQL_IDENTIFIER);
            addColumn(t, "TABLE_SCHEMA", SQL_IDENTIFIER);
            addColumn(t, "TABLE_NAME", SQL_IDENTIFIER);        // not null
            addColumn(t, "COLUMN_NAME", SQL_IDENTIFIER);       // not null
            addColumn(t, "PRIVILEGE_TYPE", CHARACTER_DATA);    // not null
            addColumn(t, "IS_GRANTABLE", YES_OR_NO);           // not null

            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_COLUMN_PRIVILEGES].name, false,
                SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[] {
                2, 3, 4, 5, 6, 1, 0
            }, false);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);

// calculated column values
        String  tableCatalog;
        String  tableSchema;
        String  tableName;
        Grantee granteeObject;

// intermediate holders
        //User     user;
        Iterator tables;
        Table    table;
        Object[] row;

// column number mappings
        int grantor        = 0;
        int grantee        = 1;
        int table_catalog  = 2;
        int table_schema   = 3;
        int table_name     = 4;
        int column_name    = 5;
        int privilege_type = 6;
        int is_grantable   = 7;

        // enumerations
        OrderedHashSet grantees =
            session.getGrantee().getGranteeAndAllRolesWithPublic();

// Initialization
        tables = allTables();

        while (tables.hasNext()) {
            table        = (Table) tables.next();
            tableName    = table.getName().name;
            tableCatalog = database.getCatalogName().name;
            tableSchema  = table.getSchemaName().name;

            for (int i = 0; i < grantees.size(); i++) {
                granteeObject = (Grantee) grantees.get(i);

                OrderedHashSet rights =
                    granteeObject.getAllDirectPrivileges(table);
                OrderedHashSet grants =
                    granteeObject.getAllGrantedPrivileges(table);

                if (!grants.isEmpty()) {
                    grants.addAll(rights);

                    rights = grants;
                }

                for (int j = 0; j < rights.size(); j++) {
                    Right right          = (Right) rights.get(j);
                    Right grantableRight = right.getGrantableRights();

                    for (int k = 0; k < Right.privilegeTypes.Length; k++) {
                        OrderedHashSet columnList =
                            right.getColumnsForPrivilege(
                                table, Right.privilegeTypes[k]);
                        OrderedHashSet grantableList =
                            grantableRight.getColumnsForPrivilege(table,
                                Right.privilegeTypes[k]);

                        for (int l = 0; l < columnList.size(); l++) {
                            QNameManager.QName fullName = ((QNameManager.QName) columnList.get(l));

                            row                 = t.getEmptyRowData();
                            row[grantor] = right.getGrantor().getName().name;
                            row[grantee] = right.getGrantee().getName().name;
                            row[table_catalog]  = tableCatalog;
                            row[table_schema]   = tableSchema;
                            row[table_name]     = tableName;
                            row[column_name]    = fullName.name;
                            row[privilege_type] = Right.privilegeNames[k];
                            row[is_grantable] =
                                right.getGrantee() == table.getOwner()
                                || grantableList.contains(fullName) ? "YES"
                                                                    : "NO";

                            try {
                                t.insertSys(store, row);
                            } catch (CoreException ) {}
                        }
                    }
                }
            }
        }

        return t;
    }

    /**
     * The SEQUENCES view has one row for each external sequence
     * generator. <p>
     *
     * <b>Definition:</b> <p>
     *
     * <pre class="SqlCodeExample">
     *
     *      SEQUENCE_CATALOG     VARCHAR NULL,
     *      SEQUENCE_SCHEMA      VARCHAR NULL,
     *      SEQUENCE_NAME        VARCHAR NOT NULL,
     *      DATA_TYPE            CHARACTER_DATA
     *      DATA_TYPE            CHARACTER_DATA
     *      NUMERIC_PRECISION    CARDINAL_NUMBER
     *      NUMERIC_PRECISION_RADIX CARDINAL_NUMBER
     *      NUMERIC_SCALE        CARDINAL_NUMBER
     *      MAXIMUM_VALUE        VARCHAR NOT NULL,
     *      MINIMUM_VALUE        VARCHAR NOT NULL,
     *      INCREMENT            VARCHAR NOT NULL,
     *      CYCLE_OPTION         VARCHAR {'YES', 'NO'},
     *      START_WITH           VARCHAR NOT NULL,
     *      DECLARED_DATA_TYPE   CHARACTER_DATA
     *      DECLARED_NUMERIC_PRECISION CARDINAL_NUMBER
     *      DECLARED_NUMERIC_SCLAE     CARDINAL_NUMBER
     *
     * </pre>
     *
     * <b>DESCRIPTION:</b><p>
     *
     * <ol>
     * <li> The values of SEQUENCE_CATALOG, SEQUENCE_SCHEMA, and
     *      SEQUENCE_NAME are the catalog name, unqualified schema name,
     *      and qualified identifier, respectively, of the sequence generator
     *      being described. <p>
     *
     * <li> The values of SEQUENCE_CATALOG, SEQUENCE_SCHEMA, SEQUENCE_NAME, and
     *      DTD_IDENTIFIER are the values of OBJECT_CATALOG, OBJECT_SCHEMA,
     *      OBJECT_NAME, and DTD_IDENTIFIER, respectively, of the row in
     *      DATA_TYPE_DESCRIPTOR (not yet implemented) that describes the data
     *      type of the sequence generator. <p>
     *
     * <li> The values of MAXIMUM_VALUE, MINIMUM_VALUE, and INCREMENT are the
     *      character representations of maximum value, minimum value,
     *      and increment, respectively, of the sequence generator being
     *      described. <p>
     *
     * <li> The values of CYCLE_OPTION have the following meanings: <p>
     *
     *      <table border cellpadding="3">
     *          <tr>
     *              <td nowrap>YES</td>
     *              <td nowrap>The cycle option of the sequence generator
     *                         is CYCLE.</td>
     *          <tr>
     *              <td nowrap>NO</td>
     *              <td nowrap>The cycle option of the sequence generator is
     *                         NO CYCLE.</td>
     *          </tr>
     *      </table> <p>
     *
     * <li> The value of START_WITH is HSQLDB-specific (not in the SQL 200n
     *      spec).  <p>
     *
     *      It is the character representation of the START WITH value. <p>
     *
     * <li> The value of NEXT_VALUE is HSQLDB-specific (not in the SQL 200n)<p>
     *      This is the character representation of the value that
     *      would be generated by NEXT VALUE FOR when this sequence
     *      is materialized in an SQL statement. <p>
     * </ol>
     *
     * @return Table
     */
    protected Table SEQUENCES() {

        Table t = sysTables[ID_SEQUENCES];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_SEQUENCES]);

            addColumn(t, "SEQUENCE_CATALOG", SQL_IDENTIFIER);
            addColumn(t, "SEQUENCE_SCHEMA", SQL_IDENTIFIER);
            addColumn(t, "SEQUENCE_NAME", SQL_IDENTIFIER);
            addColumn(t, "DATA_TYPE", CHARACTER_DATA);
            addColumn(t, "NUMERIC_PRECISION", CARDINAL_NUMBER);
            addColumn(t, "NUMERIC_PRECISION_RADIX", CARDINAL_NUMBER);
            addColumn(t, "NUMERIC_SCALE", CARDINAL_NUMBER);
            addColumn(t, "MAXIMUM_VALUE", CHARACTER_DATA);
            addColumn(t, "MINIMUM_VALUE", CHARACTER_DATA);
            addColumn(t, "INCREMENT", CHARACTER_DATA);
            addColumn(t, "CYCLE_OPTION", YES_OR_NO);
            addColumn(t, "DECLARED_DATA_TYPE", CHARACTER_DATA);
            addColumn(t, "DECLARED_NUMERIC_PRECISION", CARDINAL_NUMBER);
            addColumn(t, "DECLARED_NUMERIC_SCLAE", CARDINAL_NUMBER);

            // HSQLDB-specific
            addColumn(t, "START_WITH", CHARACTER_DATA);
            addColumn(t, "NEXT_VALUE", CHARACTER_DATA);

            // order SEQUENCE_CATALOG, SEQUENCE_SCHEMA, SEQUENCE_NAME
            // false PK, as CATALOG may be null
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_SEQUENCES].name, false, SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[] {
                0, 1, 2
            }, false);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);

        //
        int sequence_catalog           = 0;
        int sequence_schema            = 1;
        int sequence_name              = 2;
        int data_type                  = 3;
        int numeric_precision          = 4;
        int numeric_precision_radix    = 5;
        int numeric_scale              = 6;
        int maximum_value              = 7;
        int minimum_value              = 8;
        int increment                  = 9;
        int cycle_option               = 10;
        int declared_data_type         = 11;
        int declared_numeric_precision = 12;
        int declared_numeric_scale     = 13;
        int start_with                 = 14;
        int next_value                 = 15;

        //
        Iterator       it;
        Object[]       row;
        NumberSequence sequence;

        it = database.schemaManager.databaseObjectIterator(
            SchemaObjectTypes.SEQUENCE);

        while (it.hasNext()) {
            sequence = (NumberSequence) it.next();

            if (!session.getGrantee().isAccessible(sequence)) {
                continue;
            }

            row = t.getEmptyRowData();

            NumberType type = (NumberType) sequence.getDataType();
            int radix =
                (type.typeCode == Types.SQL_NUMERIC || type.typeCode == Types
                    .SQL_DECIMAL) ? 10
                                  : 2;

            row[sequence_catalog] = database.getCatalogName().name;
            row[sequence_schema]  = sequence.getSchemaName().name;
            row[sequence_name]    = sequence.getName().name;
            row[data_type]        = sequence.getDataType().getFullNameString();
            row[numeric_precision] =
                ((int) type.getPrecision());
            row[numeric_precision_radix]    = (radix);
            row[numeric_scale]              = 0;
            row[maximum_value] =(sequence.getMaxValue()).ToString();
            row[minimum_value] = (sequence.getMinValue()).ToString();
            row[increment] = (sequence.getIncrement()).ToString();
            row[cycle_option]               = sequence.isCycle() ? "YES"
                                                                 : "NO";
            row[declared_data_type]         = row[data_type];
            row[declared_numeric_precision] = row[numeric_precision];
            row[declared_numeric_scale]     = row[declared_numeric_scale];
            row[start_with] = (sequence.getStartValue()).ToString();
            row[next_value]                 = (sequence.peek()).ToString();

            t.insertSys(store, row);
        }

        return t;
    }

    Table SYSTEM_SEQUENCES() {

        Table t = sysTables[ID_SYSTEM_SEQUENCES];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_SYSTEM_SEQUENCES]);

            addColumn(t, "SEQUENCE_CATALOG", SQL_IDENTIFIER);
            addColumn(t, "SEQUENCE_SCHEMA", SQL_IDENTIFIER);
            addColumn(t, "SEQUENCE_NAME", SQL_IDENTIFIER);
            addColumn(t, "DATA_TYPE", CHARACTER_DATA);
            addColumn(t, "NUMERIC_PRECISION", CARDINAL_NUMBER);
            addColumn(t, "NUMERIC_PRECISION_RADIX", CARDINAL_NUMBER);
            addColumn(t, "NUMERIC_SCALE", CARDINAL_NUMBER);
            addColumn(t, "MAXIMUM_VALUE", CHARACTER_DATA);
            addColumn(t, "MINIMUM_VALUE", CHARACTER_DATA);
            addColumn(t, "INCREMENT", CHARACTER_DATA);
            addColumn(t, "CYCLE_OPTION", YES_OR_NO);
            addColumn(t, "DECLARED_DATA_TYPE", CHARACTER_DATA);
            addColumn(t, "DECLARED_NUMERIC_PRECISION", CARDINAL_NUMBER);
            addColumn(t, "DECLARED_NUMERIC_SCLAE", CARDINAL_NUMBER);

            // HSQLDB-specific
            addColumn(t, "START_WITH", CHARACTER_DATA);
            addColumn(t, "NEXT_VALUE", CHARACTER_DATA);

            // order SEQUENCE_CATALOG, SEQUENCE_SCHEMA, SEQUENCE_NAME
            // false PK, as CATALOG may be null
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_SYSTEM_SEQUENCES].name, false,
                SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[] {
                0, 1, 2
            }, false);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);

        //
        int sequence_catalog           = 0;
        int sequence_schema            = 1;
        int sequence_name              = 2;
        int data_type                  = 3;
        int numeric_precision          = 4;
        int numeric_precision_radix    = 5;
        int numeric_scale              = 6;
        int maximum_value              = 7;
        int minimum_value              = 8;
        int increment                  = 9;
        int cycle_option               = 10;
        int declared_data_type         = 11;
        int declared_numeric_precision = 12;
        int declared_numeric_scale     = 13;
        int start_with                 = 14;
        int next_value                 = 15;

        //
        Iterator       it;
        Object[]       row;
        NumberSequence sequence;

        it = database.schemaManager.databaseObjectIterator(
            SchemaObjectTypes.SEQUENCE);

        while (it.hasNext()) {
            sequence = (NumberSequence) it.next();

            if (!session.getGrantee().isAccessible(sequence)) {
                continue;
            }

            row = t.getEmptyRowData();

            NumberType type = (NumberType) sequence.getDataType();
            int radix =
                (type.typeCode == Types.SQL_NUMERIC || type.typeCode == Types
                    .SQL_DECIMAL) ? 10
                                  : 2;

            row[sequence_catalog] = database.getCatalogName().originalName;
            row[sequence_schema] = sequence.getSchemaName().originalName;
            row[sequence_name] = sequence.getName().originalName;
            row[data_type]        = sequence.getDataType().getFullNameString();
            row[numeric_precision] =
                ((int) type.getPrecision());
            row[numeric_precision_radix]    = (radix);
            row[numeric_scale]              = 0;
            row[maximum_value] = (sequence.getMaxValue()).ToString();
            row[minimum_value] = (sequence.getMinValue()).ToString();
            row[increment] = (sequence.getIncrement()).ToString();
            row[cycle_option]               = sequence.isCycle() ? "YES"
                                                                 : "NO";
            row[declared_data_type]         = row[data_type];
            row[declared_numeric_precision] = row[numeric_precision];
            row[declared_numeric_scale]     = row[declared_numeric_scale];
            row[start_with] = (sequence.getStartValue()).ToString();
            row[next_value] = (sequence.peek()).ToString();

            t.insertSys(store, row);
        }

        return t;
    }

/*
    WHERE ( GRANTEE IN ( 'PUBLIC', CURRENT_USER )
    OR GRANTEE IN ( SELECT ROLE_NAME FROM ENABLED_ROLES )
    OR GRANTOR = CURRENT_USER
    OR GRANTOR IN ( SELECT ROLE_NAME FROM ENABLED_ROLES ) )

*/

/**
     * The TABLE_PRIVILEGES view has one row for each visible access
     * right for each accessible table definied within this database. <p>
     *
     * Each row is a table privilege description with the following columns: <p>
     *
     * <pre class="SqlCodeExample">
     * GRANTOR      VARCHAR   grantor of access
     * GRANTEE      VARCHAR   grantee of access
     * TABLE_CATALOG    VARCHAR   table catalog
     * TABLE_SCHEMA  VARCHAR   table schema
     * TABLE_NAME   VARCHAR   table name
     * PRIVILEGE_TYPE    VARCHAR   { "SELECT" | "INSERT" | "UPDATE" | "DELETE" | "REFERENCES" | "TRIGGER" }
     * IS_GRANTABLE VARCHAR   { "YES" | "NO" }
     * WITH_HIERARCHY   { "YES" | "NO" }
     * </pre>
     *
     * @return a <code>Table</code> object describing the visible
     *        access rights for each accessible table
     *        defined within this database
     */
    Table TABLE_PRIVILEGES() {

        Table t = sysTables[ID_TABLE_PRIVILEGES];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_TABLE_PRIVILEGES]);

            addColumn(t, "GRANTOR", SQL_IDENTIFIER);           // not null
            addColumn(t, "GRANTEE", SQL_IDENTIFIER);           // not null
            addColumn(t, "TABLE_CATALOG", SQL_IDENTIFIER);
            addColumn(t, "TABLE_SCHEMA", SQL_IDENTIFIER);
            addColumn(t, "TABLE_NAME", SQL_IDENTIFIER);        // not null
            addColumn(t, "PRIVILEGE_TYPE", CHARACTER_DATA);    // not null
            addColumn(t, "IS_GRANTABLE", YES_OR_NO);           // not null
            addColumn(t, "WITH_HIERARCHY", YES_OR_NO);

            //
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_TABLE_PRIVILEGES].name, false, SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[] {
                0, 1, 2, 3, 4, 5, 6
            }, false);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);

        // calculated column values
        String  tableCatalog;
        String  tableSchema;
        String  tableName;
        Grantee granteeObject;
        String  privilege;

        // intermediate holders
        Iterator tables;
        Table    table;
        Object[] row;

        // column number mappings
        int grantor        = 0;
        int grantee        = 1;
        int table_catalog  = 2;
        int table_schema   = 3;
        int table_name     = 4;
        int privilege_type = 5;
        int is_grantable   = 6;
        int with_hierarchy = 7;
        OrderedHashSet grantees =
            session.getGrantee().getGranteeAndAllRolesWithPublic();

        tables = allTables();

        while (tables.hasNext()) {
            table        = (Table) tables.next();
            tableName = table.getName().originalName;
            tableCatalog = table.getCatalogName().originalName;
            tableSchema = table.getSchemaName().originalName;

            for (int i = 0; i < grantees.size(); i++) {
                granteeObject = (Grantee) grantees.get(i);

                OrderedHashSet rights =
                    granteeObject.getAllDirectPrivileges(table);
                OrderedHashSet grants =
                    granteeObject.getAllGrantedPrivileges(table);

                if (!grants.isEmpty()) {
                    grants.addAll(rights);

                    rights = grants;
                }

                for (int j = 0; j < rights.size(); j++) {
                    Right right          = (Right) rights.get(j);
                    Right grantableRight = right.getGrantableRights();

                    for (int k = 0; k < Right.privilegeTypes.Length; k++) {
                        if (!right.canAccessFully(Right.privilegeTypes[k])) {
                            continue;
                        }

                        privilege           = Right.privilegeNames[k];
                        row                 = t.getEmptyRowData();
                        row[grantor] = right.getGrantor().getName().originalName;
                        row[grantee] = right.getGrantee().getName().originalName;
                        row[table_catalog]  = tableCatalog;
                        row[table_schema]   = tableSchema;
                        row[table_name]     = tableName;
                        row[privilege_type] = privilege;
                        row[is_grantable] =
                            right.getGrantee() == table.getOwner()
                            || grantableRight.canAccessFully(
                                Right.privilegeTypes[k]) ? "YES"
                                                         : "NO";
                        row[with_hierarchy] = "NO";

                        try {
                            t.insertSys(store, row);
                        } catch (CoreException ) {}
                    }
                }
            }
        }

        return t;
    }

    public Table TABLES() {

        Table t = sysTables[ID_TABLES];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[ID_TABLES]);

            addColumn(t, "TABLE_CATALOG", SQL_IDENTIFIER);
            addColumn(t, "TABLE_SCHEMA", SQL_IDENTIFIER);
            addColumn(t, "TABLE_NAME", SQL_IDENTIFIER);
            addColumn(t, "TABLE_TYPE", CHARACTER_DATA);
            addColumn(t, "SELF_REFERENCING_COLUMN_NAME", SQL_IDENTIFIER);
            addColumn(t, "REFERENCE_GENERATION", CHARACTER_DATA);
            addColumn(t, "USER_DEFINED_TYPE_CATALOG", SQL_IDENTIFIER);
            addColumn(t, "USER_DEFINED_TYPE_SCHEMA", SQL_IDENTIFIER);
            addColumn(t, "USER_DEFINED_TYPE_NAME", SQL_IDENTIFIER);
            addColumn(t, "IS_INSERTABLE_INTO", YES_OR_NO);
            addColumn(t, "IS_TYPED", YES_OR_NO);
            addColumn(t, "COMMIT_ACTION", CHARACTER_DATA);

            //
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_TABLES].name, false, SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[] {
                0, 1, 2,
            }, false);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);

        // intermediate holders
        Iterator  tables;
        Table     table;
        Object[]  row;
        int table_catalog                = 0;
        int table_schema                 = 1;
        int table_name                   = 2;
        int table_type                   = 3;
        int self_referencing_column_name = 4;
        int reference_generation         = 5;
        int user_defined_type_catalog    = 6;
        int user_defined_type_schema     = 7;
        int user_defined_type_name       = 8;
        int is_insertable_into           = 9;
        int is_typed                     = 10;
        int commit_action                = 11;

        // Initialization
        tables = allTables();

        // Do it.
        while (tables.hasNext()) {
            table = (Table) tables.next();

            if (!isAccessibleTable(table)) {
                continue;
            }

            row                = t.getEmptyRowData();
            row[table_catalog] = database.getCatalogName().name;
            row[table_schema]  = table.getSchemaName().name;
            row[table_name]    = table.getName().name;

            switch (table.getTableType()) {

                case TableBase.SYSTEM_TABLE :
                case TableBase.VIEW_TABLE :
                    row[table_type]         = "VIEW";
                    row[is_insertable_into] = "NO";
                    break;

                case TableBase.TEMP_TABLE :
                case TableBase.TEMP_TEXT_TABLE :
                    row[table_type]         = "GLOBAL TEMPORARY";
                    row[is_insertable_into] = "YES";
                    break;

                default :
                    row[table_type]         = "BASE TABLE";
                    row[is_insertable_into] = table.isWritable() ? "YES"
                                                                 : "NO";
                    break;
            }

            row[self_referencing_column_name] = null;
            row[reference_generation]         = null;
            row[user_defined_type_catalog]    = null;
            row[user_defined_type_schema]     = null;
            row[user_defined_type_name]       = null;
            row[is_typed]                     = "NO";
            row[commit_action] = table.isTemp()
                                 ? (table.onCommitPreserve() ? "PRESERVE"
                                                             : "DELETE")
                                 : null;

            t.insertSys(store, row);
        }

        return t;
    }

// -----------------------------------------------------------------------------
// SQL SCHEMATA BASE TABLE

    /**
     * Retrieves a <code>Table</code> object naming the accessible catalogs
     * defined within this database. <p>
     *
     * Each row is a catalog name description with the following column: <p>
     *
     * <pre class="SqlCodeExample">
     * TABLE_CAT   VARCHAR   catalog name
     * </pre> <p>
     *
     * @return a <code>Table</code> object naming the accessible
     *        catalogs defined within this database
     */
    Table INFORMATION_SCHEMA_CATALOG_NAME() {

        Table t = sysTables[ID_INFORMATION_SCHEMA_CATALOG_NAME];

        if (t == null) {
            t = createBlankTable(
                sysTableHsqlNames[ID_INFORMATION_SCHEMA_CATALOG_NAME]);

            addColumn(t, "CATALOG_NAME", SQL_IDENTIFIER);    // not null

            // order:  TABLE_CAT
            // true PK
            QNameManager.QName name = QNameManager.newInfoSchemaObjectName(
                sysTableHsqlNames[ID_INFORMATION_SCHEMA_CATALOG_NAME].name,
                false, SchemaObjectTypes.INDEX);

            t.createPrimaryKey(name, new int[]{ 0 }, true);

            return t;
        }

        PersistentStore store = database.persistentStoreCollection.getStore(t);
        Object[]        row   = t.getEmptyRowData();

        row[0] = database.getCatalogName().name;

        t.insertSys(store, row);

        return t;
    }

    }
}